Placing text in vba for a worksheet formula?

[N_Mitch]

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:

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:

=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

 

[Beate Schmitz]

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]

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]

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]

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:

Cells(intRowNum2 + 2, intColNum).Value = "=ROUND((VLOOKUP(" & """" & txtRate1 & """"

This seems to work.

Thanks so much for your advice!

Regards,

Mitch