none
Is this string correctly formed? RRS feed

  • 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.

    Wednesday, October 28, 2015 5:33 PM

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
    Wednesday, October 28, 2015 7: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
    Wednesday, October 28, 2015 10:01 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
    Wednesday, October 28, 2015 7: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.

    Wednesday, October 28, 2015 9:37 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
    Wednesday, October 28, 2015 10:01 PM