Answered by:
Is this string correctly formed?
Question

Good morning all.
I have a macro which was written years ago, and in using it today I've found that it's not working as I'd expect.
As I've looked through it, I found something that I think is the problem.
Would you please tell me if this string is correctly formed?
ActiveCell.FormulaR1C1 = "=CONCATENATE(""X"",RC[5],"" Y"",RC[4])"
=CONCATENATE("X",F1," Y",G1)
I'm expecting the output to be that equation, but I'm not receiving that result.
TYIA.
Answers

The formula that you get depends on the location of the active cell. The formula will refer to the cells 5 and 4 columns to the left of the active cell. So if the active cell is K1, the code will result in the formula
=CONCATENATE("X",F1," Y",G1)
But if the active cell is L3, the code will result in the formula
=CONCATENATE("X",G3," Y",H3)
If you explicitly want to set the formula to =CONCATENATE("X",F1," Y",G1) regardless of the location of the active cell, use either
ActiveCell.Formula = "=CONCATENATE(""X"",$F$1,"" Y"",$G$1)"
or
ActiveCell.FormulaR1C1 = "=CONCATENATE(""X"",R1C6,"" Y"",R1C7)"
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by SteveDB1 Friday, November 6, 2015 3:34 PM

Yep. If you want to place the formula in another cell than the active cell, so that you won't have to select it, you must specify it explicitly, e.g.
Range("K1").FormulaR1C1 = "=CONCATENATE(""X"",RC[5],"" Y"",RC[4])"
This will place the formula =CONCATENATE("X",F1," Y",G1) in K1 even if K1 is not the active cell.
If you want to place the formula in another cell than K1, it's better to use Formula instead of FormulaR1C1. For example, to place the formula in G23:
Range("G23").Formula = "=CONCATENATE(""X"",$F$1,"" Y"",$G$1)"
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by David_JunFeng Friday, November 6, 2015 8:11 AM
 Marked as answer by SteveDB1 Friday, November 6, 2015 3:34 PM
All replies

The formula that you get depends on the location of the active cell. The formula will refer to the cells 5 and 4 columns to the left of the active cell. So if the active cell is K1, the code will result in the formula
=CONCATENATE("X",F1," Y",G1)
But if the active cell is L3, the code will result in the formula
=CONCATENATE("X",G3," Y",H3)
If you explicitly want to set the formula to =CONCATENATE("X",F1," Y",G1) regardless of the location of the active cell, use either
ActiveCell.Formula = "=CONCATENATE(""X"",$F$1,"" Y"",$G$1)"
or
ActiveCell.FormulaR1C1 = "=CONCATENATE(""X"",R1C6,"" Y"",R1C7)"
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by SteveDB1 Friday, November 6, 2015 3:34 PM

The formula that you get depends on the location of the active cell. The formula will refer to the cells 5 and 4 columns to the left of the active cell. So if the active cell is K1, the code will result in the formula
=CONCATENATE("X",F1," Y",G1)
But if the active cell is L3, the code will result in the formula
=CONCATENATE("X",G3," Y",H3)
If you explicitly want to set the formula to =CONCATENATE("X",F1," Y",G1) regardless of the location of the active cell, use either
ActiveCell.Formula = "=CONCATENATE(""X"",$F$1,"" Y"",$G$1)"
or
ActiveCell.FormulaR1C1 = "=CONCATENATE(""X"",R1C6,"" Y"",R1C7)"
Regards, Hans Vogelaar (http://www.eileenslounge.com)
Hi Hans.
Thank you again for your time and response.
Ok, so that code is based on the location of the active cell, and if the cell picked was at any other location, it'd result in the equation referencing another location, relative to its position. That makes sense.
Just so I have this clear, the code would require that the specific cell be selected beforehand to ensure that the F1 and G1 would be correctly chosen?
Again, thank you.

Yep. If you want to place the formula in another cell than the active cell, so that you won't have to select it, you must specify it explicitly, e.g.
Range("K1").FormulaR1C1 = "=CONCATENATE(""X"",RC[5],"" Y"",RC[4])"
This will place the formula =CONCATENATE("X",F1," Y",G1) in K1 even if K1 is not the active cell.
If you want to place the formula in another cell than K1, it's better to use Formula instead of FormulaR1C1. For example, to place the formula in G23:
Range("G23").Formula = "=CONCATENATE(""X"",$F$1,"" Y"",$G$1)"
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by David_JunFeng Friday, November 6, 2015 8:11 AM
 Marked as answer by SteveDB1 Friday, November 6, 2015 3:34 PM