Getting a cell address from Vlookup function in a macro?
 

Does anyone know how to get the cells address from a vlookup funtion in
a macro, Im relatively new to excel and learning fast, but keep comin
up against brick walls!

What I have done, is a lookup sheet where i type in a number and if its
found in a table in another sheet, it returns values from other columns,
ie name/address, but i want to be able to select the name from my lookup
sheet and mark it by painting the cell, the painting bit i can do, i
just cant figure out how to get the cells address from the vlookup
function, any ideas?

ie,

Sheets("Lookup").Select
Range("G5").Select ' my lookup value is in this box
' c[-6]:c[3] = A:J
ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C7,'Phone
List'!C[-6]:C[3],6,FALSE)"

' i have place a button on the sheet with this code:

Sheets("Phone List").Select
'Range(" --- here i need an address --- ").Select
ActiveCell.Select
' paint yellow
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
' go back to lookup sheet
Sheets("Lookup").Select


please help me .... thanks


--
mickle026
------------------------------------------------------------------------
mickle026's Profile: http://www.excelforum.com/member.php...o&userid=35518
View this thread: http://www.excelforum.com/showthread...hreadid=552904

Getting a cell address from Vlookup function in a macro?
 

wouldn't this be easier? Modify to suit. NO selections necessary. Works with
your cursor anywhere in the workbook

Sub colorvlookupcell()
whattofind = Sheets("sheet17").Range("g5")
Sheets("sheet18").Columns(1).Find(whattofind) _
.Offset(, 1).Interior.ColorIndex = 6
End Sub

--
Don Guillett
SalesAid Software

"mickle026" wrote
in message ...

Does anyone know how to get the cells address from a vlookup funtion in
a macro, Im relatively new to excel and learning fast, but keep comin
up against brick walls!

What I have done, is a lookup sheet where i type in a number and if its
found in a table in another sheet, it returns values from other columns,
ie name/address, but i want to be able to select the name from my lookup
sheet and mark it by painting the cell, the painting bit i can do, i
just cant figure out how to get the cells address from the vlookup
function, any ideas?

ie,

Sheets("Lookup").Select
Range("G5").Select ' my lookup value is in this box
' c[-6]:c[3] = A:J
ActiveCell.FormulaR1C1 = "=VLOOKUP(R5C7,'Phone
List'!C[-6]:C[3],6,FALSE)"

' i have place a button on the sheet with this code:

Sheets("Phone List").Select
'Range(" --- here i need an address --- ").Select
ActiveCell.Select
' paint yellow
With Selection.Interior
ColorIndex = 6
Pattern = xlSolid
End With
' go back to lookup sheet
Sheets("Lookup").Select


please help me .... thanks


--
mickle026
------------------------------------------------------------------------
mickle026's Profile:
http://www.excelforum.com/member.php...o&userid=35518
View this thread: http://www.excelforum.com/showthread...hreadid=552904

Getting a cell address from Vlookup function in a macro?
 

Thank You very much, sure beats searching the sheet with a for next
loop!


--
mickle026
------------------------------------------------------------------------
mickle026's Profile: http://www.excelforum.com/member.php...o&userid=35518
View this thread: http://www.excelforum.com/showthread...hreadid=552904

Getting a cell address from Vlookup function in a macro?
 

glad to help

--
Don Guillett
SalesAid Software

"mickle026" wrote
in message ...

Thank You very much, sure beats searching the sheet with a for next
loop!


--
mickle026
------------------------------------------------------------------------
mickle026's Profile:
http://www.excelforum.com/member.php...o&userid=35518
View this thread: http://www.excelforum.com/showthread...hreadid=552904