Search row, create formula below found "search" name in header, copy formula down to last row

Question

Starting in cell B5, search ROW for "Current Month". In this column starting in cell below "Current Month" Header (L6 this time), create a formula that references one column to left of this column, "=K6".

This formula will continue down the column until the last row. This column will change every month due to the fact that the newest month financial results will be added every month. Currently, MainAccount, DEPT, Dep Lookup, PROD, PROD_LINE, RD_CAT, January, February, March, April, Current Month, and YTD are the current headers as I am working on results for April month end. "Current Month" is currently in column L of sheet, next month, it will be in M. Basically, this is providing me the current months charges that will be used to update a variety of pivot tables where I don't have to change the monthly data every month.

I think I am getting tripped up at the ActiveCell portion of my code, but I am not sure.

    Range "B5").Select                                                           Range(Selection, Selection.End xltoRight)).Select                               
Selection.Find(What:="Current Month", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate                     
ActiveCell.Offset(1).Select                                                     
ActiveCell.FormulaR1C1 = "=RC[-1]"                                              

LR2 = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng2 = Range(ActiveCell & LR2)

ActiveCell.Select
Selection.Copy
Rng2.Select
ActiveSheet.Paste

In the Current Month Column, there should be a formula all the way down the page that references the last month. In this case, Current Month header is located in L5, the formula would start in L6, which would be =k6, and then copied down to last row, and it would only copy formula in empty rows only so that I don't lose my headers as there are many ranges on sheet that are tied to pivot tables.

Generally, it's best practice to avoid `ActiveCell` and `Select`. I're recommend reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It'll take your code to the next level. — BigBen, May 06 '19 at 19:42

GMalc · Answer 1 · 2019-05-07T20:34:28.233

0

Assign your find to a range variable and just copy the entire column, offset 1 column, and insert.

Dim fHdr As Range, LR2 As Long

With ThisWorkbook.Sheets("Sheet1") '    
    Set fHdr = .Cells.Find(What:="Current Month", After:=.Cells(5, 2), _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, MatchCase:=False)

    LR2 = Cells(Rows.Count, "A").End(xlUp).Row

    If fHdr Is Nothing Then
        Exit Sub

    Else
        With fHdr
            .Offset(1, 1).Resize(LR2 - 2).FormulaR1C1 = "=RC[-1]"
        End With
    End If
End With

edited May 07 '19 at 20:34

answered May 06 '19 at 22:53

GMalc

2,608
1
9
16

Thank you @GMalc. This was helpful, but it is adding an entirely new column to my worksheet that looks exactly like the column with Current Month along with no formula in Current Month column that is =cell left of current cell or ActiveCell.FormulaR1C1 = "=RC[-1]" . – Denise Turan May 07 '19 at 00:18
@DeniseTuran, Sorry I misunderstood what you were asking, please see my updated ansere. – GMalc May 07 '19 at 20:35

Search row, create formula below found "search" name in header, copy formula down to last row

1 Answers1