CODEWITHSUNDEEP
vbaexcel
Yang Chun
Yang Chun

Reputation: 25

Can't get the tabs value (name) in VBA macro

I'm stuck here. below is my vba macro. I want to lookup againts other tabs but the result only give me
=VLOOKUP(BE2, $BQ$2:$BQ$6, 1, 0)
but not the tabs name.. Why? How can I get the sheets name?

Set myValues = Application.InputBox("Please select a:", Type:=8)
Set myResults = Application.InputBox("Please select a next sheet:", Type:=8)

On Error Resume Next
Set myValues = myValues.Offset
FirstRow = myValues.Row
FinalRow = Cells(65536, myResults.Column).End(xlUp).Row

Range("BF2").Formula = _
"=VLOOKUP(" & Cells(FirstRow, myValues.Column).Address(False, False) & ", " & _
"  " & myResults.Address & " , 1, 0)"

Upvotes: 1

Views: 111

Answers (2)

Steve S
Steve S

Reputation: 431

The .Address property only returns the row and column reference unless you set the External parameter to True. See below:

Range("BF2").Formula = _
"=VLOOKUP(" & Cells(FirstRow, myValues.Column).Address(False, False) & ", " & _
"  " & myResults.Address(External:=True) & " , 1, 0)"

If you don't want the workbook name in the reference, you can build the reference using the .Worksheet.Name property of the Range object. See below:

Range("BF2").Formula = _
"=VLOOKUP(" & Cells(FirstRow, myValues.Column).Address(False, False) & ", " & _
"  " & myResults.Worksheet.Name & "!" & myResults.Address & " , 1, 0)"

See the Range.Address Property on MSDN for more information.

Upvotes: 0

L42
L42

Reputation: 19737

You're close. You just need to set the Address External argument to True.
Something like:

myResults.Address(External:=True)

Upvotes: 1

Related Questions