Using a Variable inside a VBA inserted Formula

Question

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.

Er, where's the &? Because that's what you want. But also, you want .formula, not .value. Edit: oh, just do dhlcarrier not dhlcarrier value Edit2: you don't need to set your variable to a cell. Simply do it in code like dhlcarriervalue=93. — findwindow
– findwindow, Commented May 27, 2016 at 15:00
I added the comment probably after you saw this post while you were typing, but I did try that with & and the value that gets returned in the cell is " & dhlcarriervalue & " This is what the code is at the moment, doesn't work .Formula = "=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""))))" — purplefolder
– purplefolder, Commented May 27, 2016 at 15:02
.formula = "=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""))))" — findwindow
– findwindow, Commented May 27, 2016 at 15:03
findwindow - it shows up as "#NAME" because it's in single quotes. I thought to make it double quotes by adding "& " & around it but that didn't work. You can see it almost works, but it needs to be in quotes. "=IF(LEFT(S2,2)="92","ups",IF(LEFT(S2,2)="93",dhlglobal,IF(LEFT(S2,2)="94","usps",IF(LEFT(S2,2)="1Z","ups","Check Tracking"))))" — purplefolder
– purplefolder, Commented May 27, 2016 at 15:05
I just tried it and worked for me... the .select is on the next line. I will edit. — findwindow
– findwindow, Commented May 27, 2016 at 15:07

Chrismas007 · Accepted Answer · 2016-05-27 15:13:31Z

2

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

edited May 27, 2016 at 15:13

answered May 27, 2016 at 15:07

Chrismas007

6,1154 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

purplefolder Over a year ago

The variable is declared earlier in the sub, this sub does a bunch of other things but I'm just showing for example. As I said, the cell value variable is defined in the settings sheet.

Scott Craner Over a year ago

instead of copy paste .Value = .Value?

purplefolder Over a year ago

I will declare sheet as you suggested, no problem. but this still doesn't work.

purplefolder Over a year ago

That did it. Why didn't I think of that? I use CHAR(39) extensively in this sheet. didn't think to put it in there, but I noticed "CHAR(34)" won't work, has to be "CHR". Why is it that you can't use CHAR in a formula?

Chrismas007 Over a year ago

@purplefolder VBA uses Chr while Excel uses Char... odd but that is the trick.

Collectives™ on Stack Overflow

Using a Variable inside a VBA inserted Formula

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related