select a cell following vlookup
 

I am trying to find a value and then select the cell found.
the following does not work because of "range(res).select"
any idea? Thanks
(I am not using the Find function because the range "table" includes
functions)

Dim res As Variant
Dim table As Range
Dim myVal As Range
Set myVal = Range("LastQTR")
Set table = Range("table")

res = Application.HLookup(myVal, table, 2, False)
If IsError(res) Then
MsgBox "not found"
Else
range(res).select

End If
--
caroline

select a cell following vlookup
 

res will be a value, like 36, or "BOB", not a range object, so
Range(res).Select doesn't work. You can do this:
On Error Resume Next
res = Application.HLookup(MyVal, table, 2, False)
Set ans = table.Offset(1).Resize(1, table.Columns.Count).Find(res)
If Err.Number < 0 then
Msgbox "Not Found"
Else
ans.Select
End If

"caroline" wrote:

I am trying to find a value and then select the cell found.
the following does not work because of "range(res).select"
any idea? Thanks
(I am not using the Find function because the range "table" includes
functions)

Dim res As Variant
Dim table As Range
Dim myVal As Range
Set myVal = Range("LastQTR")
Set table = Range("table")

res = Application.HLookup(myVal, table, 2, False)
If IsError(res) Then
MsgBox "not found"
Else
range(res).select

End If
--
caroline

select a cell following vlookup
 

Dim res As Variant, res1 as Variant
Dim table As Range
Dim myVal As Range
Set myVal = Range("LastQTR")
Set table = Range("table")

'res = Application.HLookup(myVal, table, 2, False)
res1 = Application.Match(myVal,Table.Rows(1),False)
If IsError(res1) Then
MsgBox "not found"
Else
Table(1).Cells(2,res1).select
End If

--
Regards,
Tom Ogilvy

"caroline" wrote:

I am trying to find a value and then select the cell found.
the following does not work because of "range(res).select"
any idea? Thanks
(I am not using the Find function because the range "table" includes
functions)

Dim res As Variant
Dim table As Range
Dim myVal As Range
Set myVal = Range("LastQTR")
Set table = Range("table")

res = Application.HLookup(myVal, table, 2, False)
If IsError(res) Then
MsgBox "not found"
Else
range(res).select

End If
--
caroline

select a cell following vlookup
 

Thanks a lot to both of you. it works
--
caroline


"Tom Ogilvy" wrote:


Dim res As Variant, res1 as Variant
Dim table As Range
Dim myVal As Range
Set myVal = Range("LastQTR")
Set table = Range("table")

'res = Application.HLookup(myVal, table, 2, False)
res1 = Application.Match(myVal,Table.Rows(1),False)
If IsError(res1) Then
MsgBox "not found"
Else
Table(1).Cells(2,res1).select
End If

--
Regards,
Tom Ogilvy

"caroline" wrote:

I am trying to find a value and then select the cell found.
the following does not work because of "range(res).select"
any idea? Thanks
(I am not using the Find function because the range "table" includes
functions)

Dim res As Variant
Dim table As Range
Dim myVal As Range
Set myVal = Range("LastQTR")
Set table = Range("table")

res = Application.HLookup(myVal, table, 2, False)
If IsError(res) Then
MsgBox "not found"
Else
range(res).select

End If
--
caroline