2

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.
Improve this question
9
  • 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. 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""))))" 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""))))" 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"))))" 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. Commented May 27, 2016 at 15:07

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.
instead of copy paste .Value = .Value?
I will declare sheet as you suggested, no problem. but this still doesn't work.
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?
@purplefolder VBA uses Chr while Excel uses Char... odd but that is the trick.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.