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

[blk]

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

 

[Cbrine]

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]

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]

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]

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]

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]

Ben,

Does something like this work?

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]

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