copy range of formulas when cell is empty

**nikenis** · 05-09-2012, 04:31 AM

Dear all,

I am trying to fill up a range with the range row-1 where one or several cells are empty.

When running a macro in Staff Listing, it updates the Report sheet with new employees details range(A:J). The range(K:T) pull datas through formula. I need to copy range(K:T) for each new rows containing information in range(A:A)
The file attached gives a better idea of what I am after.

Thanks.

**arlu1201** · 05-09-2012, 04:43 AM

Use this code

 
Sub copy_formulae()
Dim lrow As Long
Dim i As Long

With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row

    For i = 6 To lrow
        If .Range("A" & i).Value <> "" Then
            .Range("K" & i & ":T" & i).Formula = .Range("K" & i - 1 & ":T" & i - 1).Formula
        End If
    Next i
End With

End Sub

**nikenis** · 05-10-2012, 02:43 AM

Thanks Arlette,
It works well but I would like to modify it as the formula uses indirect and The reference cell needs to be incremented accordingly.

Cheers

**arlu1201** · 05-17-2012, 07:14 AM

After changing the references, i realised that the references still dont get incremented with the earlier code. Use this instead:

Option Explicit

Sub copy_formulae()
Dim lrow As Long
Dim i As Long

With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    
    For i = 6 To lrow
        If .Range("A" & i).Value <> "" Then
            .Range("K" & i-1 & ":T" & i).FillDown
        End If
    Next i
End With

End Sub

**arlu1201** · 05-17-2012, 07:09 AM

If you need the references to increment, you will need to change the absolute referencing to relative referencing in your formula. For e.g. you have month($W$22) as a small part of your formula. Change it to $W22.