Calling Rob van Gelder - Help needed with Vlookup Macro again
Ian,
Try
Sub CopyFormula()
With Range("C2")
.Formula = "=IF($A2="""","""",VLOOKUP($A2," & _
"PARTNUMBERS!$A$2:$B$1000,2,FALSE))"
.AutoFill Range("C2:C1000")
End With
End Sub
the
" & _
"
part is not necessary, you can have the formula in one line.
I brake it in to 2 lines to avoid the line warp in the message.
Cecil
"Ian" wrote in message
...
Hi Rob
Hope you don't mind me posting this again but last week I posted a message
asking for
help to convert a Vlookup Formula to a Macro and you kindly replied with
your Macro.
I copied and pasted your Macro into mine but it didn't work, even after
editing it slightly.
I think the problem was I didn't make it clear what I wanted the Macro to
do, so I have
tried to explain it a bit clearer, and hope you can come up with the
winning Macro.
We use Excel 2000 to book work in and out, in the "BOOK IN" Worksheet we
enter the Part Numbers for each job, down in Column A, (one in each Row),
I then
have the following Formula pasted into each Cell down in Column C that
looks up the
Part Number that was entered into the Cell in Column A, and it returns the
correct
Part Description in the Cell in Column C on the same Row.
=IF($A2="","",VLOOKUP($A2,PARTNUMBERS!$A$2:$B$1000 ,2,FALSE))
The list of Part Numbers and Part Descriptions are in the Worksheet named
"PARTNUMBERS"
where all the Part Numbers are down Column A, and the corresponding Part
Descriptions
are down Column B.
This Formula works well but I would really like to convert it to a Macro
so that the Formulas
don't get accidently deleted as there are a few people who will be using
the Spreadsheet.
I have tried to protect Column C to stop these Formulas from being
accidently deleted, but then
it also stops us from cutting the Cells from the "BOOK IN" Worksheet and
pasting them to the
"BOOK OUT" Worksheet when the Parts has been repaired and are being booked
out.
Can you help.
TIA
Ian
|