Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Vlookup Formula to Macro
Hello
I have the following Formula that I have pasted into each Cell down in Column C, so when a Part Number is entered into a Cell in Column A, the correct Part Description for that Part Number is then looked up in the PARTNUMBERS worksheet, and the correct Part Description is then automatically put into the cell in Column C. =IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000 ,2,FALSE)) I found this Formula on a newsgroup and edited it to suit my worksheet so it probably could be fine tuned further to suit my needs but it works ok. What I would like to do is to convert this Formula into a Macro. Can anyone help. Cheers Ian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Vlookup Formula to Macro
I find it strange that you're doing a lookup of A2 in a range which include
A2. It's going to find a match straight away. Vlookup: VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000,2,FALSE)) The above formula in plain english: Find A2 in range PARTNUMBERS!$A$2:$A$1000 and return the value in the same row of column 2 (ie. return the value in B2:B1000) Most VLookups have the last argument as False, meaning exact match. Code as requested: Sub test() With Range("C2") If IsEmpty(Range("A2").Value) Then .Value = "" Else .Value = Application.VLookup(Range("A2"), _ Range("PARTNUMBERS!$A$2:$B$1000"), 2, False) End If End With End Sub -- Rob van Gelder - http://www.vangelder.co.nz/excel "Ian" wrote in message ... Hello I have the following Formula that I have pasted into each Cell down in Column C, so when a Part Number is entered into a Cell in Column A, the correct Part Description for that Part Number is then looked up in the PARTNUMBERS worksheet, and the correct Part Description is then automatically put into the cell in Column C. =IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000 ,2,FALSE)) I found this Formula on a newsgroup and edited it to suit my worksheet so it probably could be fine tuned further to suit my needs but it works ok. What I would like to do is to convert this Formula into a Macro. Can anyone help. Cheers Ian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Vlookup Formula to Macro
"Rob van Gelder" wrote in message ...
I find it strange that you're doing a lookup of A2 in a range which include A2. It's going to find a match straight away. Hi Rob Thanks for your quick reply. Each Part Number is entered into cells down in Column A in "sheet1", it then looks up the Part Number and Part Description in "sheet2", (renamed to PARTNUMBERS) between Cells A2 & B1000, and then the corresponding Part Description is entered into Column C back in "sheet1". So down Column A, we type the Part Numbers when they come in for repair, and we will type in different Part Numbers in different Rows down Column A. Code as requested: Sub test() With Range("C2") If IsEmpty(Range("A2").Value) Then .Value = "" Else .Value = Application.VLookup(Range("A2"), _ Range("PARTNUMBERS!$A$2:$B$1000"), 2, False) End If End With End Sub I pasted the above code, but didn't work, I obviously wasn't very clear in my original post. The lines - " Range("A2") " and " Range("C2") " are obviously pointing to specific Cells, (A2 and C2), how do I specify the complete Column A and Column C. Thanks for your help Ian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text to formula using VLookup | Excel Worksheet Functions | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
Excel 2002: Can I convert #N/A to 0 in the VLOOKUP( ) formula ? | Excel Discussion (Misc queries) | |||
MACRO: Convert Row Formula to Value | Excel Discussion (Misc queries) | |||
convert a formula into a macro | Excel Discussion (Misc queries) |