CODEWITHSUNDEEP
vbaexcelexcel-formula
purplefolder
purplefolder

Reputation: 57

Using a Variable inside a VBA inserted Formula

SOLUTION thanks to Chrismas007 was using the "CHR(34)" in the formula.

I have this code which inserts a formula that reads from adjacent cell values and decides what to put in the cell. E.g. if the cell has a tracking number that starts with "93" then we know it's a "dhlglobalmail" package.

However, I need to have these values easily changeable by the user if something changes in the future so I have the values in a "settings" worksheet in a cell range.

I attempted to declare a variable for just one of the carriers as a test called "dhlcarriervalue" which is assigned to the cell range on another sheet which contains the text "dhlglobalmail". When I attempt to put the variable in the sub INTO the formula, it won't work. I just get errors. I tried putting "&" around it, double quotes, single quotes, it just won't work. I know you need to use double quotes for strings in VBA formulas, but this is a variable not a string. I tried removing them and it won't work. You can see the other numbers work, which are just strings, but when I try to use a variable, it doesn't work. Is this not possible? I know the variable is correct because I can "msgbox" it and it's there.

Dim LastRowNum As Long
Dim dhlcarriervalue As String
dhlcarriervalue = ThisWorkbook.Sheets("Settings").Range("B41")
With Range("R2:" & "R" & LastRowNum)
.Value = "=IF(LEFT(s2,2)=""92"",""ups"",IF(LEFT(s2,2)=""93"","dhlcarriervalue",IF(LEFT(s2,2)=""94"",""usps"",IF(LEFT(s2,2)=""1Z"",""ups"",""Check Tracking""))))"
.Select
.Copy
.PasteSpecial xlPasteValues
End With
' I tried "" & dhlcarrier value & "" which didn't work either.

Upvotes: 2

Views: 560

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

I've made some notes throughout your code:

Dim LastRowNum As Long '<Where is this defined???
Dim dhlcarriervalue As String

dhlcarriervalue = ThisWorkbook.Sheets("Settings").Range("B41").Value '.Value is default, but you should specify
With Sheets("NAME OF SHEET").Range("R2:" & "R" & LastRowNum) 'what Sheet specifically?
.Formula = "=IF(LEFT(s2,2)=""92"",""ups"",IF(LEFT(s2,2)=""93""," & Chr(34) & dhlcarriervalue & Chr(34) & ",IF(LEFT(s2,2)=""94"",""usps"",IF(LEFT(s2,2)=""1Z"",""ups"",""Check Tracking""))))"
'.Select 'Not needed since you specify the cell for .Copy
.Copy
.PasteSpecial xlPasteValues
End With

Upvotes: 2

Related Questions