# Placing text in vba for a worksheet formula?

#### N_Mitch

##### Board Regular
G'day,

I have a sheet which calculates backpay figures for employees. It is initially entered in through a userform, where hourly rates, hours, penalty rates and all other payroll data needed for this purpose is copied.

Anyway, here is part of my code, where I insert a worksheet formula into a cell:
Code:
``````Cells(intRowNum2 + 2, intColNum).Value = "=ROUND((VLOOKUP(" & txtRate1 & _
",Rates!A:E,5,FALSE)*" & lbxr1 & "*" & txtOrdHrs1.Value & _
")+(VLOOKUP(" & txtRate2 & ",Rates!A:E,5,FALSE)*" & lbxr2 & "*" & _
txtOrdHrs2.Value & ")+(VLOOKUP(" & txtRate3 & ",Rates!A:E,5,FALSE)*" & _
lbxr3 & "*" & txtOrdHrs3.Value & ")+VLOOKUP(" & txtRate4 _
& ",Rates!A:E,5,FALSE)*" & lbxr4 & "*" & txtOrdHrs4 & ")+VLOOKUP(" _
& txtRate5 & ",Rates!A:E,5,FALSE)*" & lbxr5 & "*" & txtOrdHrs5 & "),2)"``````
This code ends up being:
Code:
``=ROUND((VLOOKUP(X10,Rates!A:E,5,FALSE)*1*2)+(VLOOKUP(X10,Rates!A:E,5,FALSE)*1*2)+(VLOOKUP(X10,Rates!A:E,5,FALSE)*1*2)+(VLOOKUP(X10,Rates!A:E,5,FALSE)*1*2)+(VLOOKUP(X10,Rates!A:E,5,FALSE)*1*2),2)``
Now, the VLOOKUP ("X10" in this case) is the code for the pay scale; however, Excel reads this as a cell reference instead of text.

If I manually place the X10 within inverted commas (i.e. VLOOKUP("X10"...), the formual works.

"Why not just copy the formula result instead of the whole calculation?" I hear you say? Well, we are audited annually by the State Government, and our auditors need to be able to both verify our calculations and be able to re-produce them themselves. (I also have a boss who doubts modern technology and wants to see for himself how and why the figures are what they are!) It would be a whole lot easier if I did not need to input worksheet formulae, but that is not an option.

I have found a work-around, by removing the X from the pay rate; however, is there a way to place the inverted commas within vba? (Or, Is there a way to copy the TextBox value of txtRate4 to be read by Excel as text?)

Thanks,

Mitch

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hello Mitch,

try:

<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" >=ROUND(VLOOKUP(""X10"",Rates!A:E,5,0)*2+VLOOKUP(""X10"",Rates!A:E,5,0)*2+VLOOKUP(""X10"",Rates!A:E,5,0)*2+VLOOKUP(""X10"",Rates!A:E,5,0)*2+VLOOKUP(""X10"",Rates!A:E,5,0)*2,2)
</span></nobr></div>

By the way, does this shorter formula do the same?:
<div style="background-color:#FFFFFF; border-width:2px; border-style: groove; border-color:#ff9966; padding:4px;"><nobr><span style="font-family:Courier New,Arial; font-size:9pt ;" >=ROUND(VLOOKUP(""X10"",Rates!A:E,5,0)*10,2)
</span></nobr></div>

G'day Beate,

Your formula may be shorter for the example I gave, but each rate variable in vba may hold a different hourly rate (i.e. txtRate1, txtRate2, txtRate3, txtRate4, txtRate5) - hence the need for a VLOOKUP() for each txtRate.

I suspect that there is no way to include quotation marks (i.e. " " ) within a vba text statement because vba reads each mark as either a beginning or an end of text.

Regards,

Mitch

Hello Mitch,

didn't you notice that you have to double the "" when using it in VBA, e.g. ""X10"" as per my example?

G'day Beate,

I did miss the idea you were trying to get across.

So, applying this to my initial vba, I came up with this syntax:

Code:
``Cells(intRowNum2 + 2, intColNum).Value = "=ROUND((VLOOKUP(" & """" & txtRate1 & """"``
This seems to work.

Regards,

Mitch

Replies
3
Views
179
Replies
0
Views
120
Replies
4
Views
252
Replies
9
Views
811
Replies
1
Views
421

1,221,006
Messages
6,157,344
Members
451,417
Latest member
Ilu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back