R1C1 formula help

[Ody]

I have the following code:

Dim LastSheet As String
LastSheet = Worksheets(Worksheets.Count).Name
With Worksheets("Summary")

lMaxRows = .Cells(.Rows.Count, "AA").End(xlUp).Row
.Range("aa" & lMaxRows + 1).Formula = "='" & LastSheet & "'!Bx7"

lMaxRows = .Cells(.Rows.Count, "AB").End(xlUp).Row
.Range("AB" & lMaxRows + 1).Formula = "='" & LastSheet & "'!Bx8"

ActiveCell.Offset(0, 10).FormulaR1C1 = "=RC27 & RC28"

My goal is to reference two cells from another sheet, then combine them at the bottom of my table in column "H". I'm having trouble with merging them, it seems to work once then I can't get it to give me the same results.

Thoughts?


ps
This is the best board on the net

 

[Jerry Sullivan]

Hi Ody,

The first part of the code is putting references to the last sheet into the next row after the last data cell in Column AA and the next row after the last data cell in Column AB.

Assuming that these two cells are in the same row (which might not always be true), then the formula to combine them would be:

.Cells(lMaxRows+1,"H").FormulaR1C1 = "=RC27 & RC28"

The last statement in your posted code...

ActiveCell.Offset(0, 10).FormulaR1C1 = "=RC27 & RC28"

...will give you unpredictable results because ActiveCell isn't explicitly defined.

Good luck!