Hello,
VBA scripting query.
What I'm trying to do is find the ROW number for a specified time increment on a specific column (just a number) so that I can call this row number up later on. However, when I use the below script as part of my WorksheetFunction
I continually get either N/A or no result even though I can quite clearly see the Time value in column I:I. In fact, even if I attempt to do the same above directly within the worksheet it will only seem to find the row number if the TIME value that I specify is within the first 5, or so, rows.
I kept thinking that it may be a floating point issue but I'm not using these numbers within an equation so they should no have an floating point error accumulations.
I have resorted to using the below script:
In this code I am forced to use the MATCH(TIME,I:I,1) calling to even find any value (using -1 doesn't work either) but this has a seemingly random distribution of values as it can either be above, below or equal to the TIME value that I am trying to locate. So I had to include additional IF checks to say "IF less than TIME then add 1 row number" , "IF equal to TIME then don't add any row numbers" etc but this doesn't even work!! It will use use the "LESS THAN" function even though the output for the solution is, in some instances, the correct row number so it should have used the "EQUAL TO" Function.
It's driving me nuts trying to get this to work.
Can anyone help me?
VBA scripting query.
What I'm trying to do is find the ROW number for a specified time increment on a specific column (just a number) so that I can call this row number up later on. However, when I use the below script as part of my WorksheetFunction
Code:
Match(TIME, Range("I:I"), 0)I continually get either N/A or no result even though I can quite clearly see the Time value in column I:I. In fact, even if I attempt to do the same above directly within the worksheet it will only seem to find the row number if the TIME value that I specify is within the first 5, or so, rows.
I kept thinking that it may be a floating point issue but I'm not using these numbers within an equation so they should no have an floating point error accumulations.
I have resorted to using the below script:
Code:
If Cells(15, 4) >= Cells(7, 4) Then
Dim Time_Row As Integer
Dim Time_Row_Output As Integer
On Error Resume Next
Time_Row = Application.WorksheetFunction.Match(TIME, Range("I:I"), 1)
On Error GoTo 0
If Time_Row > 0 Then
If Cells(Time_Row, 9) - TIME = 0 Then
Time_Row_Output = Time_Row
Cells(28, 2) = "Equals 0" ' THESE ARE JUST PROOF CHECKS THAT ARE DISPLAYED WITHIN THE WORKSHEET
Cells(32, 2) = Time_Row_Output
Cells(33, 2) = Cells(Time_Row_Output, 9)
ElseIf Cells(Time_Row, 9) - TIME< 0 Then
Time_Row_Output = Time_Row + 1
Cells(28, 2) = "Less than 0" ' THESE ARE JUST PROOF CHECKS THAT ARE DISPLAYED WITHIN THE WORKSHEET
Cells(32, 2) = Time_Row_Output
Cells(33, 2) = Cells(Time_Row_Output, 9)
ElseIf Cells(Time_Row, 9) - TIME > 0 Then
Time_Row_Output = Time_Row - 1
Cells(28, 2) = "Greater Than 0" ' THESE ARE JUST PROOF CHECKS THAT ARE DISPLAYED WITHIN THE WORKSHEET
Cells(32, 2) = Time_Row_Output
Cells(33, 2) = Cells(Time_Row_Output, 9)
End If
Else
Time_Row_Output = 5 ' This is the row number that would occur if the initial starting time is above the specified time
End If
Cells(35, 2) = Time_Row_Output
Cells(36, 2) = Cells(Time_Row_Output, 9)
End IfIn this code I am forced to use the MATCH(TIME,I:I,1) calling to even find any value (using -1 doesn't work either) but this has a seemingly random distribution of values as it can either be above, below or equal to the TIME value that I am trying to locate. So I had to include additional IF checks to say "IF less than TIME then add 1 row number" , "IF equal to TIME then don't add any row numbers" etc but this doesn't even work!! It will use use the "LESS THAN" function even though the output for the solution is, in some instances, the correct row number so it should have used the "EQUAL TO" Function.
It's driving me nuts trying to get this to work.
Can anyone help me?
Last edited:
