Index and Match - Errors
 

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

Index and Match - Errors
 

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

Index and Match - Errors
 

Thank you for your response.
I copy/pasted your code and got Error 400 - Application-defined or
object-defined error
on line : Set TargetRange = .Range("B2:B") & LastRow
I do not understand why.
I take note of your solution without using Index.
Thank you again.

Index and Match - Errors
 

I resolved my previous Error by changing this line :
Set TargetRange = .Range("B2:B") & LastRow
To :
Set TargetRange = .Range(.Cells(2, "B"), .Cells(.Rows.Count,
"B").End(xlUp))
..
But now I have a Mismatch Error on :
Set FoundRow = Application.Match(CDbl(OriginalTarget),
TargetRange, 1) + 1
..
You were suspicious yourself on the Dim FoundRow as Variant
I understand you converted my OriginalTarget to double because I
indeed could have values with decimals there.
FoundRow being Dimmed as Variant, I do not see how I am going to
resolve this Mismatch.
Thank you for your help.

Index and Match - Errors
 

It should have been:

Set TargetRange = .Range("B2:B" & LastRow)



On 10/17/2010 18:06, u473 wrote:
Thank you for your response.
I copy/pasted your code and got Error 400 - Application-defined or
object-defined error
on line : Set TargetRange = .Range("B2:B")& LastRow
I do not understand why.
I take note of your solution without using Index.
Thank you again.

--
Dave Peterson

Index and Match - Errors
 

Remove the "Set" keyword.



On 10/17/2010 20:18, u473 wrote:
I resolved my previous Error by changing this line :
Set TargetRange = .Range("B2:B")& LastRow
To :
Set TargetRange = .Range(.Cells(2, "B"), .Cells(.Rows.Count,
"B").End(xlUp))
.
But now I have a Mismatch Error on :
Set FoundRow = Application.Match(CDbl(OriginalTarget),
TargetRange, 1) + 1
.
You were suspicious yourself on the Dim FoundRow as Variant
I understand you converted my OriginalTarget to double because I
indeed could have values with decimals there.
FoundRow being Dimmed as Variant, I do not see how I am going to
resolve this Mismatch.
Thank you for your help.

--
Dave Peterson

Index and Match - Errors
 

Thank you. It works.
Have a good day,
J.P.