.FormulaR1C1 with variable

[KG Old Wolf]

Goal: use "If" formula in R1C1 to evaluate cell contents; if "true" use Variable, if "false" use Constant

Issue: Constant works fine in all code snippets. Variable works fine when a number (Work_2) but FAILS when text (Work_1). Following are 3 different approaches I've tried to no avail. I've spent a full day researching and testing and got nowhere getting the string variable to work. The formula that appears in the cell is the correct string value (Old Wolf) but it is equating to a #NAME error message because it is not in quotes.

Recommendations for correct syntax and reading lists will be greatly appreciated. Here are the code snippets. What am I missing?

Thanks
--------------------------------------------------------------

Dim Work_1 As String
Dim Work_2 As Long
Dim Hope As String
'
Work_1 = "Old Wolf"
Work_2 = 2468
'
'
Range(Cells(10, 2), Cells(14, 2)).FormulaR1C1 = "=IF(R[-8]C[-1]=3,Work_1,R[-8])"
'
'
Range(Cells(10, 3), Cells(14, 3)).FormulaR1C1 = "=IF(R[-8]C[-2]=3," & Work_1 & ",R[-8])"
'
'
Hope = "=if(R[-8]=3," & Work_1 & ",R[-8])"
Range(Cells(10, 1), Cells(14, 1)).FormulaR1C1 = Hope
'

 

[pgc01]

Hi

In a formula a string must always be inside double quotes. Try:

Range(Cells(10, 3), Cells(14, 3)).FormulaR1C1 = "=IF(R[-8]C[-2]=3,""" & Work_1 & """,R[-8])"

Remarks:

1 - I don't understand what the formula does. It's returning a whole row()?

2 - If you don't mind this is a remark about notation

Your code would be more readable if you could know the types of the variables when you look at them

Dim Work_1 As String
[COLOR=black]Dim Work_2 As Long[/COLOR]

This is confusing to me. The names are almost the same and with different types.

I'd use something like:

Dim [B]s[/B]Work_1 As String
[COLOR=black]Dim [B]l[/B]Work_2 As Long[/COLOR]

or

Dim [B]str[/B]Work_1 As String
[COLOR=black]Dim [B]lng[/B]Work_2 As Long[/COLOR]

This way when you read the code you neve have doubts that one is a Long and the other a String.

 

[KG Old Wolf]

Dear PGC,

First - surrounding the string variable with (3) """ on either side of the named variable does indeed solve the problem (I had tried two "" but not a total of three).

Thanks for the solution and your recommendation for a standard naming conveniton. I will incorporate them going forward (I have been writing VBA for about a month now and had not settled on a standard naming convention).

As for the purpose of the formula, it just takes a formula in the R1C1 format and "ripples" it down through the cells in my specified range. It returns a calculated result for each cell in the range. The calulation is a prototype to develop a technique (which works with a numeric variable but not a string).

Thanks again