Reference Dynamic Column in Formula Not Using Relative References.

What are the alternative options to using relative references?

I was previously using:

Range("U3:U120").Formula = "=IF(RC[-17]=""12"", RC[-11]*0.888, """")"

However this is no good now. I'm using variables and I would like to reference a variable column in the If statement. I can no longer count these columns as they are changing. What I would like to do is refer to the column dynamically as a variable.
I do not think the IF statement allows variables in it's commands, what would be a good alternative to my requirements?

Concatenate the column variable into the formula.

'Column number
Col1 = 4
Col2 = 10
Range("U3:U120").Formula = "=IF(RC" & Col1 & "=""12"", RC" & Col2 & "*0.888, """")"


'Column Letter
Col1 = "D"
Col2 = "J"
Range("U3:U120").Formula = "=IF(" & Col1 & "3=12, " & Col2 & "3*0.888, """")"

Thanks AlphaFrog, I've entered as the following:

Range("U3:U120").Formula = "=IF(VariableOne=""12"", LengthColumn*0.888, """")"

However this is showing as #NAME in the specified column, so I'm not sure if it's recognising the actual variable columns.

The syntax of your attempt doesn't match either of the examples I gave. Maybe if you just coped one of the examples and just change the variable names to suit.

Thanks AlpahFrog,

E.g. 1
Range("U3:U120").Formula = "=IF(RC" & Col1 & "=""12"", RC" & Col2 & "*0.888, """")"

This returns #NAME in the cells.

E.g. 2
Range("U3:U120").Formula = "=IF(" & Col1 & "3=12, " & Col2 & "3*0.888, """")"

Returns blank cells, where the forumla should be run and results should be returned. I'm not sure what 3 stands for in the formula, part of me thought it may be the row number but I"m hoping that's not the case. I then altered your formula removing the 3's but that returned blank cells:

Range("U3:U120").Formula = "=IF(" & Col1 & "=12, " & Col2 & "*0.888, """")"

Thanks again for your input.

In your situation, what do Col1 and Col2 equal; a column letter or a column number?

When you used...
Range("U3:U120").Formula = "=IF(" & Col1 & "3=12, " & Col2 & "3*0.888, """")"
...what was the formula in cell U3 ? Was it the formula you wanted?


The 3 does represent the first row the formula references. Excel will automatically adjust the formula for each cell below.

The two versions of codes I gave you in post #2 duplicates exactly the formula that your code in post #1 creates.

Thanks AlphaFrog, Col1 and Col2 represent Letters which are set as strings.

When you used...
Range("U3:U120").Formula = "=IF(" & Col1 & "3=12, " & Col2 & "3*0.888, """")"
...what was the formula in cell U3 ? Was it the formula you wanted?
Post#1 - Range("U3:U120").Formula = "=IF(RC[-17]=""12"", RC[-11]*0.888, """")" References each cell dynamically by relatively referencing the column range U3:U120; to all cells in the column.

Something must be up as the new formula is not working, I really didn't think one could combine the column as a variable when posting a formula throughout the column range.

Thanks again for your input, greatly appreciated and I'm obviously not in your league at programming, just trying to provide quality feedback for diagnosis purposes.

Originally Posted by Dal123

When you used...
Range("U3:U120").Formula = "=IF(" & Col1 & "3=12, " & Col2 & "3*0.888, """")"
...what was the formula in cell U3 ? Was it the formula you wanted?
Post#1 - Range("U3:U120").Formula = "=IF(RC[-17]=""12"", RC[-11]*0.888, """")" References each cell dynamically by relatively referencing the column range U3:U120; to all cells in the column.

Answer this to help diagnose the problem.

What is the value of both Col1 and Col3 ?

What is the result formula in U3 (even if it's wrong)?

Why not just use A1 notation instead of R1C1?

Range("U3:U120").Formula = "=IF(D3=""12"", J3*0.888, """")"

As mentioned in Post#1 the columns are always changing therefore A1 notation cannot be used.

If you are talking about a variable on the worksheet itself, you probably need to use INDIRECT in your formula.

I'm not sure I fully understand your requirement.

A sample workbook with an explanation referring to the "variable column" might be useful.

Originally Posted by AlphaFrog

Answer this to help diagnose the problem.

1 - What is the value of both Col1 and Col3 ?

2 - What is the result formula in U3 (even if it's wrong)?

Thank you both for your responses;

1 - Col1's value is "D", Col3 is not used in this formula, Col3 is not used in this formula, I think you mean Col2 where the value is "J".

2 - As stated in Post#5 results posted for both updates; results are the same with the code advised.

I do not think the number 3 should be in the formula as this formula is actioned over the column range of rows 3 - 120. When removing the row 3 the formula is at least actioned over the rows 3 - 120 and not just row 3 which is the reason of my Post#3 where my code did not exactly match yours for this specific reason.

As the formula is being spread over the entire column; Excel will not adjust it to suit each row independantly as the formula was previously referenced relatively to a column.

TMS I think you're making a good point with Indirect; this is the first I'm learning about Indirect so I'm looking forward to learning about it.

Ok a couple of days researching into Indirect is most interesting and a very powerful function. Watching these tutorials I think you're right TMS, a combination of VLookUp, Indirect and If statement is what I need.

Is it possible to use all of these in one formula or must I split the computation into different ones?

Double up quotation marks in VBA works like this...

In VBA, a normal string begins and ends with a quotation mark like this...
MyString = "This is a string."

If you want a quotation mark as part of the string, use two quotation marks for each literal quotation mark you want within the string. That way, VBA knows its a literal quotation and not the beginning or end of the string.

In vba...
MyString = "This is a ""string"" with quotes in it."

The result string would be ...
This is a "string" with quotes in it.


Putting a formula in a cell with VBA works the same
Range("A1").Formula = "=IF(B1="""", """", C1)"

Result formula in A1...
=IF(B1="", "", C1)