Copy Cell Down to Last Row with Dynamic Cell References

Question

I am using the Find method to identify columns which have been populated with headers. Then offsetting my selection down one row and entering either a string or a formula. Then I want to paste my selection down the column to the Last populated row. It's really just the syntax of the last line of the code I'm looking to repair.

Dim i As Integer
Dim g As Integer
i = 0
g = 0
Dim Rtitles(3) As String

Rtitles(0) = "NAME OF M4"
Rtitles(1) = "M4 NUMBER"
Rtitles(2) = "X-Ref ID"
Rtitles(3) = "ADDRESS ID"

Range("A1").Select

Do Until i = 17
    If ActiveCell <> "" Then
        ActiveCell.Offset(0, 1).Activate
        i = i + 1
    Else
        ActiveCell = Rtitles(g)
        g = g + 1
    End If
Loop

'Determines Last Row
Dim LastRow As Long
LastRow = ActiveSheet.Cells(1048575, 3).End(xlUp).Row


'This section Populates the Four newly titled columns
i = 0
Do Until i = 4

Cells.Find(What:=Rtitles(i), After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Activate
[...]
If i = 2 Then ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(FIND(""_"",RC[-1])),RC[-1],LEFT(RC[-1],LEN(RC[-1])-3))"
Range(ActiveCell).AutoFill Destination:=Range(ActiveCell, Cells(LastRow,activecolumn))

score 1 · Accepted Answer · edited May 23 '17 at 12:21

It's really just the syntax of the last line of the code I'm looking to repair.

There is no need to use AutoFill. You can enter a formula in the entire range in one go.

Replace

Range(ActiveCell).AutoFill Destination:=Range(ActiveCell, Cells(LastRow,activecolumn))

with

Dim ColName As String

'~~> Gets the Column Letter of active cell
ColName = Split(Cells(, ActiveCell.Column).Address, "$")(1)

Range(ActiveCell.Address & ":" & ColName & lastrow).Formula = _
Range(ActiveCell.Address).Formula

EDIT

BTW do not use LastRow = ActiveSheet.Cells(1048575, 3).End(xlUp).Row to find the last row. One should never hard code values. You may want to see THIS on how to find last row.

Great advice, this really helps expand my "toolbox" in VBA. – PocketLoan Aug 03 '15 at 21:06 — PocketLoan, Aug 03 '15 at 21:06

score 0 · Answer 2 · answered Jul 31 '15 at 21:08

0

To get the last row in column 3 ("C"), use:

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row

answered Jul 31 '15 at 21:08

KFichter

773
4
15

1

I don't think OP wants to know "How to find the last row" :) – Siddharth Rout Jul 31 '15 at 23:29

Copy Cell Down to Last Row with Dynamic Cell References

2 Answers2