You have several options.

You could just write the formula to the cells using VBA:

Range("C4:ALN4").Formula = "=IF(C5<>"""",COUNTA($C$5:C5),"""")"

If you want the formulas removed afterwards, you could:

With Range("C4:ALN4")
    .Formula = "=IF(C5<>"""",COUNTA($C$5:C5),"""")"
    .Value = .Value
End With

Or if you don’t like the formulas and maybe want to do other things as part of the process, you could recreate the effect using code:

Dim cnt As Long, cel As Range
For Each cel In Range("C4:ALN4")
    If cel.Offset(1) = "" Then
        cel.Value = ""
    Else
        cnt = cnt + 1
        cel.Value = cnt
    End If
Next

Record a macro as you do the following

Highlight the range you want the formula to be in
paste your formula into the formula bar N.B. this is essential – do not paste the formula into a cell
Hit Ctrl-Enter. N.B. if you just hit Enter this will not work

Stop the macro recording and examine the code for Macro1 in Module1

However, I can’t see how that formula ever worked with how you have described the problem – so make sure you get that working first, and take care with the row numbers – shouldn’t that be C4 not C5 for example

You can use the following code:

With ActiveSheet.Range("C4:ALN4")
    .Formula = "=IF(C5<>"""",COUNTA($C$5:C5),"""")"
End With

Your formula is applied to the range you specify

Formulas can be used in VBA with Application.WorksheetFunction. however, it is usually easier to use vba directly. For example the IF-statement would be more tedious, just use the vba if instead. The COUNTA-statement could be written as Application.WorksheetFunction.CountA(Range("...")) The value in the range is dependent on how you plan to call the vba function, can be by button or the Worksheet.Change-Event, manually, etc.

Assuming that the data in row 5 are constants and not the results of formulas, you can use the following solution without loops:

Sub FillWithSeries()
    Range("C5:ALN5").Find("*", Range("ALN5")).Offset(-1).Value = 1
    Range("C5:ALN5").SpecialCells(xlCellTypeConstants) _
        .Offset(-1).DataSeries , xlDataSeriesLinear
End Sub