How to change a relative cell address row, Ex: "=RC[1]&RC[2]" to absolute address in a do loop

[jkalpus]

Greetings. I need to concatenate two cells using VBA, but keep ONE of those cell address absolute (the R[1].
I also need to have the code loop through the cells until finished.
Here's what I've got so far.

Do
ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until IsEmpty(ActiveCell.Offset(0, 2)) = True

I've tried simply removing the [] which I thought turns the cell address into absolute. It's not working.
What, please, am I missing here??? I'm using Excel 2013.

 

[Michael M]

Depending whre Activecell is to start with, you wiil need to change it to...
so assuming activecell is in row 1, it will be

ActiveCell.Formula = "=B1&C1"

Might be better to post ALL the code !

 

[Rick Rothstein]

Greetings. I need to concatenate two cells using VBA, but keep ONE of those cell address absolute (the R[1].
I also need to have the code loop through the cells until finished.
Here's what I've got so far.

Do
ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]"
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until IsEmpty(ActiveCell.Offset(0, 2)) = True

I've tried simply removing the [] which I thought turns the cell address into absolute. It's not working.
What, please, am I missing here??? I'm using Excel 2013.

The square brackets are an offset from the reference cell which, in your case is the ActiveCell wherever that may be, so removing the square brackets ignores the ActiveCell reference and fixes the location at Columns A and B. I think this will place the correct formula in the ActiveCell for you...

ActiveCell.FormulaR1C1 = "=RC" & ActiveCell.Column + 1 & "&RC" & ActiveCell.Column + 2