After being prompted for a target value to be found <= in Col.
"B" (Integers)
I want to return the corresponding date on the same row in Col. "A".
My first suspicion is on my Dims, and the second one is on the syntax
of my Index.
I was trying to find the Match first, then trying to incorporate the
result in the Index. But I failed.
help appreciated
..
Sub FindTargetDate()
Dim WB As Workbook
Dim SH3 As Worksheet
Dim MyPath As String
Dim LastRow, FoundRow As Long
Dim OriginalTarget As Variant
Dim DateRange, TargetRange, FoundDate As Range
MyPath = "C:\1-Work\TestData\"
Set WB = Workbooks.Open(MyPath & "Omega.xls")
Set SH3 = WB.Worksheets("Dates")
SH3.Activate
LastRow = SH3.Cells(Rows.Count, 1).End(xlUp).Row - 1 ' Because there
is a Total row
Set TargetRange = SH3.Range("B2:B") & LastRow ' Error # 1 here. Run
Time error # 1004
Set DateRange = SH3.Range("A2:A") & LastRow
OriginalTarget = InputBox("Target: ")
FoundRow = Application.Match(OriginalTarget, TargetRange, 1) + 1
FoundDate = Application.Index(DateRange, FoundRow) ' Error # 2 Here.
Mismatch ???
SH3.Cells(20, 1) = FoundDate(FoundRow, 1).Value ' Found Date for Found
Row
SH3.Cells(20, 2) = FoundDate(FoundRow, 2).Value ' Found Value <=
OriginalTarget
SH3.Cells(20, 3) = OriginalTarget
FoundDate.Select
With Selection.Font
.Bold = True
.ColorIndex = 5
End With
End Sub