PDA

View Full Version : Find Date an return row number



Lisa_S
05-12-2015, 11:24 PM
Hello,

in the attachement you can find my excel sheet. I hope this helps.

I need a VBA code to look for date A (German format) and return the row number (9) as a variable X to further use it in a VBA script.

Additionally I want to look for date B (which can`t be found) so I want the script to return the row number of the next available date after date B which is in this case 10 and return this to variable Y.

I`ve tried a lot but couldn`t get the code done.

I hope you can help me :)

Best
Lisa

jonh
05-13-2015, 01:36 AM
Hi.


Sub test()
Debug.Print FindDt(Range("C2"), Range("B7:B20"))
Debug.Print FindDt(Range("C3"), Range("B7:B20"))
End Sub

Function FindDt(f As Range, r As Range) As Long
On Error Resume Next

FindDt = r.Find(f.Value).Row

If FindDt = 0 Then
Dim ary(), i
ary = r
For i = LBound(ary) To UBound(ary)
If ary(i, 1) >= f.Value Then
FindDt = r.Row + i - 1
Exit For
End If
Next
End If
End Function

Yongle
05-13-2015, 04:28 AM
Welcome to the forum Lisa_S
Another option for you

This checks for a match to the value in cell C2
If exact match then X contains row number
If nearest match the Y contains row number
If date input exceeds all datum values, it exits the sub.
(To apply to C3 use the same code replacing C2 with C3)

Message boxes included for information (can delete them if you want)




Sub FindDateRow()
'declare variables, set ranges and known values
Dim A, B, X, Y, MyRange, c
With ActiveSheet
A = .Range("C2").Value
B = A 'need to remember original value
Set MyRange = .Range("B7:B20")
End With
'check to ensure that date input in C2 not too great
If A > WorksheetFunction.Max(MyRange) Then
MsgBox "Input value exceeds all datum values"
Exit Sub
End If
'search for match or next highest
X = 0
Do Until X <> 0
For Each c In MyRange
If c = A Then
X = c.Row
End If
If X = 0 And c.Row = MyRange.End(xlDown).Row Then
A = A + 1
End If
Next c
Loop
If B = A Then
MsgBox "Exact Match in row " & X
Else
Y = X
X = 0
MsgBox "Nearest Match in row " & Y
End If
End Sub

Lisa_S
05-15-2015, 04:07 AM
Hey,
thank you both very much for your fast help! You`re the best! :)