I'm not sure what's going on, but when you use a line like:

Dim LastRow, FoundRow As Long

You're actually declaring FoundRow as a long, but LastRow as a variant. You can
use:

Dim LastRow as Long, FoundRow As Long

But I think this is easier to read and modify:

Dim LastRow as long
dim FoundRow As Long

The second problem you may have is that when you get input from an inputbox,
you're getting a string. And in excel, there's a difference between 1 and '1
(the number 1 and the string 1).

And if you don't find a match, you don't want to use that variable as a row
number (since it's an error).

This may get you a bit closer (or not!):

Option Explicit
Sub FindTargetDate()

Dim WB As Workbook
Dim SH3 As Worksheet
Dim MyPath As String
Dim LastRow As Long
Dim FoundRow As Variant 'could be an error
Dim OriginalTarget As String
Dim TargetRange As Range
Dim FoundDate As Range

MyPath = "C:\1-Work\TestData\"

Set WB = Workbooks.Open(MyPath & "Omega.xls")
Set SH3 = WB.Worksheets("Dates")

With SH3
'.Activate 'not necessary to activate the sheet first

LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
' Because there is a Total row

Set TargetRange = .Range("B2:B") & LastRow
' Error # 1 here. RunTime error # 1004

OriginalTarget = InputBox("Target: ")
If OriginalTarget = "" Then
Exit Sub 'user hit cancel
End If

Set FoundRow _
= Application.Match(CDbl(OriginalTarget), TargetRange, 1) + 1

If IsError(FoundRow) Then
MsgBox "No match"
Exit Sub
End If

Set FoundDate = .Cells(FoundRow + 1, "a")

.Cells(20, 1) = FoundDate.Value
.Cells(20, 2) = FoundDate.Offset(0, 2).Value
.Cells(20, 3) = OriginalTarget

End With

End Sub

Untested, but it did compile.



On 10/17/2010 16:22, u473 wrote:
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

--
Dave Peterson