Address Value in Macro based Vlookup

SLCantrell

New Member
Joined
Mar 3, 2002
Messages
4
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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,
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top