Placing text in vba for a worksheet formula?

N_Mitch

Board Regular
Joined
Jan 23, 2007
Messages
146
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Beate Schmitz

Active Member
Joined
May 20, 2007
Messages
392
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>
 

N_Mitch

Board Regular
Joined
Jan 23, 2007
Messages
146
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.

Thanks for your advice anyway, I appreciate your time.

Regards,

Mitch
 

Beate Schmitz

Active Member
Joined
May 20, 2007
Messages
392
Hello Mitch,

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

N_Mitch

Board Regular
Joined
Jan 23, 2007
Messages
146
G'day Beate,

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

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.

Thanks so much for your advice!

Regards,

Mitch
 

Forum statistics

Threads
1,181,364
Messages
5,929,547
Members
436,677
Latest member
CathalP1992

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?

Disable AdBlock

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
Top