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
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