Loop by row and then by column

[MrTreasury]

Hello Forum,

I'm stuck and require your help.

I'm currently trying to produce a macro which fills cells down by row and then move onto column. As the macro moves onto the next column, the number of rows decreases (It'll look like stairs when complete)

The general jist of the spreadsheet is related to forward rates in Foreign Currency. We have created what we want using IFERROR, but ideally want this to be done by a macro.

Here is a snippet of the macro. Apologies if this isn't well written, it is rather difficult to explain.

Currently, the code will complete one column and then end. So I'm unsure as to why the loop isn't moving into the next column, so I put a loop in a loop as per below.

Please ask away any questions.

Thanks, Mr T.

M = 9
N = 6
TimeM = 9
TimeN = 5
Maturity = ws1.Cells(M, N).Value
Time = ws1.Cells(TimeM, TimeN).Value
R = 9 ' row
O = 7
T = 9



For i = 1 To 18
    
    For j = 1 To 18

         If Time < Maturity Then
            ws1.Cells(R, O).Value = 0
         ElseIf Time >= Maturity Then
            ws1.Cells(R, O).Value = ws1.Cells(T, 3).Value / ws1.Cells(28, O).Value '''''
        Else
            ws1.Cells(R, O).Value = 0
            
        M = M + 1
        N = N + 1
        TimeM = TimeM + 1
        TimeN = TimeN + 1
        'R = R + 1
        O = O + 1
        
        
    End If
       
    Next j
    
    R = R + 1
    T = T + 1

    
Next i

End Sub

 

[eduzs]

Sub test()

x = 18
For i = 1 To 18
        For j = 1 To x
            Cells(i, j) = i
        Next j
x = x - 1
Next i

End Sub

 

[MrTreasury]

Sub test()

x = 18
For i = 1 To 18
        For j = 1 To x
            Cells(i, j) = i
        Next j
x = x - 1
Next i

End Sub

Thank you, it's now printing across the rows and columns. Unfortunately, it isn't completing the formula

 ElseIf Time >= Maturity Then
            ws1.Cells(i, j).Value = ws1.Cells(T, 3).Value / ws1.Cells(28, O).Value

Depending on which variable I change (O or T), means it either prints 0% or prints the results for the first column across the entire section.

Full For Loop + If statement:

 For i = 3 To 26
    For j = 7 To x
    On Error Resume Next
        If Time < Maturity Then
            ws1.Cells(i, j).Value = ""
        ElseIf Time >= Maturity Then
            ws1.Cells(i, j).Value = ws1.Cells(T, 3).Value / ws1.Cells(28, O).Value '
        Else
           ws1.Cells(i, j).Value = ""
        T = T + 1
        M = M + 1
        TimeM = TimeM + 1
        O = O + 1
        End If
        
    Next j
    
'T = T + 1
'O = O + 1
'x = x + 1
Next i

I'm unsure as to why it isn't repeating the

 ElseIf Time >= Maturity Then
            ws1.Cells(i, j).Value = ws1.Cells(T, 3).Value / ws1.Cells(28, O).Value

section across all the columns as I am doing both T=T+1 and O=O+1

Any thoughts, because I have been tweaking the code but I don't seem to get either.

 

[DanteAmor]

The general jist of the spreadsheet is related to forward rates in Foreign Currency. We have created what we want using IFERROR, but ideally want this to be done by a macro.

You can put the formula or formulas used, in which cells they are and what data you have on the sheet.

 

[MrTreasury]

You can put the formula or formulas used, in which cells they are and what data you have on the sheet.

All sorted now. Thanks for your help, much appreciated. I'm certain i'll be back - until then, all the best.