Inserting a string into a formula...Help!!!

blk

Board Regular
Joined
May 27, 2003
Messages
61
Hi,

I am trying to insert a string into a formula, but I keep getting an error (error 1004) message.

Here's the first few lines that makes up the string.

Dim CurrentCarrier As String
Dim ColAddress As String
Dim rng As Range
Dim ColNum As Variant
CurrentCarrier = Sheets("Drop Prices").CarrierListBox.Value & " Definitions"
If CurrentCarrier = " Definitions" Then Exit Sub
Set rng = Sheets("OLO Definitions").Range("A1:IV1")
ColAddress = "'OLO Definitions'!" & rng(1, ColNum).EntireColumn.Address

Which comes out as: 'OLO Definitions'!$D:$D



I'm trying to insert it into the following formula.....any ideas?

Sheets("Drop Prices").Range("H5").FormulaR1C1 = _
"=VLOOKUP(RC[-7],[" & ColAddress & "],1,0)"



Thanks in advance
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Not sure, but I think you are trying to insert a Range address into a R[]C[].format.

You need to set

ColAddress ="OLO Definitions'!"& rng(1, ColNum).entirecolumn.Column

Instead of address. I haven't test this syntax, and I don't usually use the RC[] notiation, so it might need a little bit of twecking, but you should be able to get the idea.

Hope this helps.
 

blk

Board Regular
Joined
May 27, 2003
Messages
61
Not working I'm afraid. Any other ideas!! I've been looking everywhere for a answer. I've done it like this as the lookup destination will always be a different column. Tried using both $D:$D and RC4, but no luck yet.
 

blk

Board Regular
Joined
May 27, 2003
Messages
61
Here's all the code:


Sub LookuponTemplate()

Dim CurrentCarrier As String
Dim ColAddress As String
Dim rng As Range
Dim ColNum As Variant
CurrentCarrier = Sheets("Drop Prices").CarrierListBox.Value & " Definitions"
If CurrentCarrier = " Definitions" Then Exit Sub
Set rng = Sheets("OLO Definitions").Range("A1:IV1")
ColNum = Application.Match(CurrentCarrier, rng, 0)

ColAddress = "'OLO Definitions'!" & rng(1, ColNum).EntireColumn.Address

Sheets("Drop Prices").Select
ActiveCell.Select
ActiveSheet.Unprotect
Range("H5:H65536").ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
If Range("A5") = False Then Exit Sub
ActiveSheet.Unprotect
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
Range("H:H").NumberFormat = "General"

Sheets("Drop Prices").Range("H5").FormulaR1C1 = _
"=VLOOKUP(RC[-7],[" & ColAddress & "],1,0)"

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
If Range("A6") = False Then Exit Sub
ActiveSheet.Unprotect
Range("H5").AutoFill Destination:=Range("H5:H" & Range("A65536").End(xlUp).Row), Type:=xlFillDefault
''Range("F4:H4").AutoFill Destination:=Range("F4:H" & Range("A65536").End(xlUp).Row), Type:=xlFillDefault
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022

ADVERTISEMENT

Cbrine's right - you're setting R1C1 formula, but using an "A1" notation address. Another solution would be to specify the notation in your call to .Address. i.e. .Address(true,true,xlR1C1).
 

blk

Board Regular
Joined
May 27, 2003
Messages
61
Thanks for that. I've changed it so the string now contains the R1C1 format. It still doesn't seem to work though.

Here's the line that makes up the string:

ColAddress = "'OLO Definitions'!" & rng(1, ColNum).EntireColumn.Address(True, True, xlR1C1)


And here's the formula:

Sheets("Drop Prices").Range("H5").FormulaR1C1 = _
"=VLOOKUP(RC[-7],[" & ColAddress & "],1,0)"


The value of ColAddress is: 'OLO Definitions'!C6

It works fine if I put it in the formula like that, but it's just not liking it as a string.

Any more help will be greatly appreciated


Thanks
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Ben,

Does something like this work?

Code:
Set rngFormulaCell = Sheets("Drop Prices").Range("H5")

ColAddress = "'OLO Definitions'!" & rng(1, ColNum).EntireColumn.Address

rngFormulaCell.Formula = "=VLOOKUP(" & rngFormulaCell.Offset(0, -7).Address & "," & ColAddress & ",1,0)"

Using an intermediary range variable (rngFormulaCell) allows you to use the Offset property to count 7 columns back in the VLOOKUP formula.

Laters,
Dan
 

blk

Board Regular
Joined
May 27, 2003
Messages
61
Thanks for that Dan. From your code, I managed to work out what was wrong with mine. I had [] surrounding " & ColAddress &". I removed them and now it works fine. Now looks like this:

Sheets("Drop Prices").Range("H5").FormulaR1C1 = _
"=VLOOKUP(RC[-7]," & ColAddress & ",1,0)"


Thanks

Ben
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

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
Top