Macro for a loop?

[pao13]

Hi. I've attached my spreadsheet. I want a macro or a function but preferably a macro for the following: In column K when the cells start being negative numbers eg. cell K5 I want Z6 to have a 1 and the same for Z7. The results I want for the macro need to be the same with the Z column (I have done it manually). In Z8 if K7 is negative to have 2 (Z9 also 2). In Z10 if K9 is negative to have 3 (Z11 also 3). When K turns positive (K17 in this case) then in Z I don't care what the number will be. But when K turns negative again (K35 in this case) I want Z36 to have a 1 again. So to "restart" the series.

Hope you understand what I'm doing. Sorry if I did not explain it correctly. English is not my native language!

 

[eibi]

Pao,

If a formula is satisfactory, I'd use something like this in cell Z6:

=IF(MOD(ROW(),2)=1,Z5,IF(K5<0,N(Z5)+1,""))

If a macro is required, I'd have some more questions before I could offer a solution.

 

[pao13]

WOW! You have answers to everything! Thanks again eibi!

 

[eibi]

If you still prefer a macro solution, try this:

Sub ColumnZ()

Dim i As Integer
Dim Value_Count As Integer
Dim Z_Incr As Integer
Dim TargetSheet As Worksheet

Set TargetSheet = Worksheets("P_L events up to 1 month")

Value_Count = WorksheetFunction.CountA(TargetSheet.Range("K:K")) - 1

Z_Incr = 1

For i = 1 To Value_Count

    If TargetSheet.Range("K" & i + 4) < 0 Then

        TargetSheet.Range("Z" & i + 5 & ":Z" & i + 6).Value = Z_Incr
    
        Z_Incr = Z_Incr + 1

    Else

        Z_Incr = 1
    
    End If

    i = i + 1
        
Next i

End Sub

See attached. (Find code in Module 1 of attached file)

NOTE: I'm fairly new to VBA, so check me to make sure that this works like you want...And keep in mind that it won't auto-update like a formula would; it will update only when the macro is executed.

 

[pao13]

I checked you! You are correct again!