Apply formulas to a column that may be overwritten after macro is run.

  • Hi, I have an existing vba see attached and code below that copies and paste columns from one sheet to another. The destination sheet has some existing columns that does a Filldown everytime the macro is run.

    Sub copypaste()

    Dim r As Range
    Dim r2 As Range
    Dim lLast_Row1 As Long
    Dim lLast_Row2 As Long
    'identify the columns and copy the data
    With Worksheets("Raw").ListObjects(1).DataBodyRange
    Set r = Application.Union(.Columns(1), .Columns(2), .Columns(7)).SpecialCells(xlCellTypeVisible)
    End With
    With Worksheets("Output")
    Set r2 = .Range("B8:J8").Resize(.Range("B6").CurrentRegion.Rows.Count)

    r.Copy r2.Cells(1, 1).Offset(-1)

    'extend the formulas
    lLast_Row1 = .Range("E" & Rows.Count).End(xlUp).Row
    lLast_Row2 = .Range("B" & Rows.Count).End(xlUp).Row

    If lLast_Row2 > lLast_Row1 Then
    .Range("E" & lLast_Row1).Resize((lLast_Row2 - lLast_Row1) + 1, 6).FillDown
    End If

    End With
    End Sub

    I have an existing Column E in the 'Output' tab, that has a formula =100%/(COUNTIF(b:b, ">0") applied. This ensures that the % allocation is equally split by default.

    However, sometimes a user may overwrite the cells in Column E with his/her own preferred portfolio allocation. Assuming the user overwrites all the formulas in Column E, my existing macro will not be able to apply the COUNTIF formula anymore as it is all overwritten..

    How can i build the formula into the vba such that it will repply the formula everytime the macro is run (see attached file)?

    thank you!

  • Re: Apply formulas to a column that may be overwritten after macro is run.

    You can add this right before the if statement that tests the last row

    .Range("E7").Formula = "=100%/(COUNTIF(B:B, "">0""))"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!