Macro fills formula in the alternate row.

[motilulla]

Using Excel 2010
Hello,

The code below fill formula in BJ7 to BJ100 Where Lastrow = 100. I need the formula fill in the alternate row for example BJ7, BJ9, BJ11 and so on. Please help.

Sub FillFormula()
Dim Lastrow As Long
Lastrow = Range("C" & Rows.Count).End(xlUp).Row
Range("BJ7:BJ" & Lastrow).Formula = "=COUNTA(BM7:CF7)"
End Sub

Regards,
Moti

 

[Joe4]

Here is one way:

    Dim r As Long
    Application.ScreenUpdating = False
    For r = 7 To 100 Step 2
        Cells(r, "BJ").FormulaR1C1 = "=COUNTA(RC[3]:RC[22])"
    Next r
    Application.ScreenUpdating = True

 

[Cubist]

    Dim Lastrow As Long
    Dim i As Long
    
    Lastrow = Range("C" & Rows.Count).End(xlUp).Row
    
    For i = 7 To Lastrow Step 2
        Range("BJ" & i).Formula = "=COUNTA(BM" & i & ":CF" & i & ")"
    Next i

 

[Cubist]

Another option with array that might be faster, NOT TESTED.

Sub FillFormulaWithArrays()
    Dim Lastrow As Long, i As Long
    Dim dataRange As Variant, resultFormula As Variant, numRows As Long
    
    Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    If (Lastrow - 6) Mod 2 = 1 Then
        numRows = Int((Lastrow - 6) / 2) + 1
    Else
        numRows = (Lastrow - 6) / 2
    End If
    
    dataRange = Range("BM7:CF" & Lastrow).Value
    ReDim resultFormula(1 To numRows, 1 To 1)
    
    For i = 1 To UBound(dataRange, 1) Step 2
        resultFormula((i + 1) / 2, 1) = WorksheetFunction.CountA(Application.Index(dataRange, i, 0))
    Next i
    
    Range("BJ7").Resize(numRows).Formula = resultFormula
End Sub

 

[motilulla]

Here is one way:

    Dim r As Long
    Application.ScreenUpdating = False
    For r = 7 To 100 Step 2
        Cells(r, "BJ").FormulaR1C1 = "=COUNTA(RC[3]:RC[22])"
    Next r
    Application.ScreenUpdating = True

Joe4, Thank you for the quick response it worked. Good Luck!

My Best Regards,
Moti

 

[motilulla]

    Dim Lastrow As Long
    Dim i As Long
   
    Lastrow = Range("C" & Rows.Count).End(xlUp).Row
   
    For i = 7 To Lastrow Step 2
        Range("BJ" & i).Formula = "=COUNTA(BM" & i & ":CF" & i & ")"
    Next i

Cubist, I like your code it is easy to translate and understand it.

Thank you for your quick response. Good Luck!

My Best Regards,
Moti