Address Value in Macro based Vlookup

[SLCantrell]

I'm having problems getting the relative address into a VLOOKUP formula inside a macro. Below are the steps taken with explanation:

Cells.Find(what:="LAB COST", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
This lets me find the column in the spreadsheet based on the header value no matter where is it put by the download program. We had added and removed so many columns that column addresses are not practical for the macro.
myaddress = ActiveCell.Address(ReferenceStyle:=x1R1C1)
In looking at the local variables my address does have the correct relative address in the field. R6C27

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>0,(RC[-1]*(VLOOKUP(myaddress,'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE))),"""")"
Selection.Copy
This is probably where my problem is with the coding of the myaddress within the formula. It is not evaluating the value but putting the ""myaddress"" into the formula.


IF(Z8>0,(Z8*(VLOOKUP(myaddress,'Cost to Chg Ratios'!$A$8:$B$22,2,FALSE))),"")

This is the result that gets put into the spreadsheet.
Any help would be appreciate.
Thanks
Sammy

 

[Barrie Davidson]

On 2002-03-04 06:41, SLCantrell wrote:
I'm having problems getting the relative address into a VLOOKUP formula inside a macro. Below are the steps taken with explanation:

Cells.Find(what:="LAB COST", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
This lets me find the column in the spreadsheet based on the header value no matter where is it put by the download program. We had added and removed so many columns that column addresses are not practical for the macro.
myaddress = ActiveCell.Address(ReferenceStyle:=x1R1C1)
In looking at the local variables my address does have the correct relative address in the field. R6C27

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>0,(RC[-1]*(VLOOKUP(myaddress,'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE))),"""")"
Selection.Copy
This is probably where my problem is with the coding of the myaddress within the formula. It is not evaluating the value but putting the ""myaddress"" into the formula.


IF(Z8>0,(Z8*(VLOOKUP(myaddress,'Cost to Chg Ratios'!$A$8:$B$22,2,FALSE))),"")

This is the result that gets put into the spreadsheet.
Any help would be appreciate.
Thanks
Sammy

Try changing your command to:

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>0,(RC[-1]*(VLOOKUP(" & myaddress & ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE))),"""")"

Regards,

 

[SLCantrell]

Barrie,
Tried it as you listed it.
With the "&myaddress&", that actually gives a compile error.
I tried with single quotes and that gives a run time error 1004, application-defined or object-defined error.
Thanks
Sammy

 

[Barrie Davidson]

On 2002-03-04 11:35, SLCantrell wrote:
Barrie,
Tried it as you listed it.
With the "&myaddress&", that actually gives a compile error.
I tried with single quotes and that gives a run time error 1004, application-defined or object-defined error.
Thanks
Sammy

Sammy, make sure you have a space separating each element. Like this:

"This text" & myaddress & "That text"

 

[SLCantrell]

Barrie,
Below is the statement as it now appears in the macro. It give me a compile error at the third " or just before ",'Cost the error is syntax error.

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(" &myaddress& ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE)"

Really Appreciate your help.
Sammy

 

[Barrie Davidson]

On 2002-03-04 14:54, SLCantrell wrote:
Barrie,
Below is the statement as it now appears in the macro. It give me a compile error at the third " or just before ",'Cost the error is syntax error.

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(" &myaddress& ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE)"

Really Appreciate your help.
Sammy

Sammy, change the statement to read:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(" & myaddress & ",'Cost to Chg Ratios'!R8C1:R22C2,2,FALSE)"

Note the space before, and after, myaddress.


Regards,

 

[SLCantrell]

Barrie,
Your great, it worked, I had left out one space.
Thanks a lot.
Sammy