Get current cell value and place formula based on it in another column
 

Hi,

I'm trying to get a macro that will allow me to get information from a
separate worksheet based on the text in the currently select cell, and
place it 5 columns to the right. I got the vlookup working fine if not
in the macro, but when i execute it It says "Method 'FormulaR1C1' of
object 'range' failed".

Help is greatly appreciated. Thanks

Francisco Roque


Sub GetPriceandCount()
'
' GetPriceandCount Macro
' Macro recorded 09/10/2006 by Francisco Roque
'
' Keyboard Shortcut: Option+Cmd+p
'
Dim itemName As String
itemName = ActiveCell.Value
ActiveCell.Offset(0, 5).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(armourName;
Database!$A$1:$P$3869;13;FALSE)/100"

Get current cell value and place formula based on it in anothercolumn
 

Your code is using .Formula (not .formular1c1).

ActiveCell.Formula _
= "=VLOOKUP(armourName,Database!$A$1:$P$3869,13,FALS E)/100"

or if using .formular1c1

ActiveCell.FormulaR1C1 _
= "=VLOOKUP(armourName,Database!r1c1:r3869c16,13,FAL SE)/100"

R1C1 is A1
R3869C16 is P3869

But notice that I changed your list separator from semicolon to comma. VBA is
USA centric.

wrote:

Hi,

I'm trying to get a macro that will allow me to get information from a
separate worksheet based on the text in the currently select cell, and
place it 5 columns to the right. I got the vlookup working fine if not
in the macro, but when i execute it It says "Method 'FormulaR1C1' of
object 'range' failed".

Help is greatly appreciated. Thanks

Francisco Roque

Sub GetPriceandCount()
'
' GetPriceandCount Macro
' Macro recorded 09/10/2006 by Francisco Roque
'
' Keyboard Shortcut: Option+Cmd+p
'
Dim itemName As String
itemName = ActiveCell.Value
ActiveCell.Offset(0, 5).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(armourName;
Database!$A$1:$P$3869;13;FALSE)/100"

--

Dave Peterson