As you can see below, I am working on making a macro that compares the time in one column on a page to another on a page. However, in the if statement where I'm trying to round the numbers with time values in it (because I need the numbers to match exactly in order for the if statement to work), I am getting a type mismatch error. Does anyone know a smarter way to code if one cell = another cell with time in it? I would use Vlookup but it is having trouble because the time values do not exactly equal each other...help?

Time on difference page is in 10 min increments...
Time on Obs at 0050Z page is hourly on the 50th minute...

Sub Get_Obs_To_Difference()

Dim TTR, TTR2, lastcell, bottom As Integer

  'Initialize variables
  lastcell = 12500
  bottom = 1
  counter = 0
  'Go through the Date Column and Place the Date
  For TTR = 4 To lastcell 'Skip the header and move to last row only on the XX50Z obs
        For TTR2 = bottom To 8515
            bottom = 1
            If Round(Sheets("OBS AT XX50Z").Range("H" & TTR2).Value, 5) = Round(Sheets("Difference").Range("F" & TTR2), 5) Then
                 Sheets("Difference").Range("I" & TTR).Value = Sheets("OBS AT XX50Z").Range("M" & TTR2).Value
                 Sheets("Difference").Range("J" & TTR).Value = Sheets("Difference").Range("I" & TTR).Value * 0.51444
                 Sheets("Difference").Range("K" & TTR).Value = Sheets("Difference").Range("G" & TTR).Value - Sheets("Difference").Range("J" & TTR).Value
               End If
        Next TTR2
        counter = counter + 1
        Sheets("Difference").Range("L4").Value = (counter) / (12500)
  Next TTR
End Sub
9 Years
Discussion Span
Last Post by TylerTCF


Coding is right but the value assigned for the variable 'bottom' is 1. 1 means it takes first row in the excel sheet that row could be a header row that is the column name so that error displays as 'Type Mismatch'.


This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.