Use variable value as part of a text formula in VBA

[spacecaptainsuperguy]

I'm working on setting up a report to be generated by a macro and in one particular cell I want to incorporate both text and numbers in a cell. Earlier in the macro, I've defined CLoan1 as Long and assigned it a value. The line of code I'm trying to get to work is the following:

ActiveCell.FormulaR1C1 = "=""Total available for borrowing or limit of "" & Text(CLoan1, ""$#,###,000"")"

But I can't figure out how to word it so that the Text formula uses the value of CLoan1 to not produce an #NAME? error.

Any suggestions on how I can do this or a better line of code to get the end result?

Thanks in advance

 

[rlv01]

Instead of just assigning the formula you are trying to create directly to the cell like this

ActiveCell.FormulaR1C1 = "=""Total available for borrowing or limit of "" & Text(CLoan1, ""$#,###,000"")"

Make life easier on yourself by declaring a string variable to hold the formula and then assign that to the cell. With the variable you can more easily use the debugger to tweak the formula until you get it right (corrected formula below).

Dim FormulaStr As String
FormulaStr = "=""Total available for borrowing or limit of "" & Text(" & Cloan1 & ", ""$#,###,000"")"
Debug.Print FormulaStr

ActiveCell.FormulaR1C1 = FormulaStr

 

[dreid1011]

Try this:

ActiveCell.FormulaR1C1 = "=""Total available for borrowing or limit of ""& Text(" & CLoan1 & ",""$#,###,000"")"

 

[spacecaptainsuperguy]

Thank you both. That got my formula sorted out.

@rlv01 I have never, nor would have ever, thought to try turning it into a string variable. I've also never used the Debug.Print before so I'll have to look at that some more to see how that can help me in the future.

Very much appreciate the help!!