CODEWITHSUNDEEP
excelvbafor-loop
MaximusPrimus
MaximusPrimus

Reputation: 35

Nested For/Do While/While not able to reach outer loop in VBA/Macro

I am trying to fill #N/As in a table of values by averaging out the values in a column of values. The logic is that from the location of #N/A, use two pointers to find the next 2 non #N/A values above and below a specific value and then average them out to fill the #N/A. Here is the code :-

Sub generate_NA_cutoffs()

Worksheets("Final cutoffs2").Activate

Dim i As Integer, j As Integer
Dim fwd As Integer, rev As Integer
Dim o As Integer

o = 15

For j = 3 To j = 6 'Go column-wise
    For i = 7 To i = 12 ' Go row-wise
        If i = 7 And Application.WorksheetFunction.IsNA(Cells(i, j).Value) = True Then
            Cells(i, j + o).Value = 0
        ElseIf i = 12 And Application.WorksheetFunction.IsNA(Cells(i, j).Value) = True Then
            Cells(i, j + o).Value = 1
        ElseIf Application.WorksheetFunction.IsNA(Cells(i, j).Value) = True Then
            fwd = i + 1
            rev = i - 1
            Do While Application.WorksheetFunction.IsNA(Cells(fwd, j).Value) = True
                fwd = fwd + 1
            Loop
            Do While Application.WorksheetFunction.IsNA(Cells(rev, j + o).Value) = True
                rev = rev - 1
            Loop
            'take the avg of 2 non NA values
            Cells(i, j + o).Value = (Cells(fwd, j).Value + Cells(rev, j + o).Value) * 0.5
        Else
            Cells(i, j + o).Value = Cells(i, j).Value
        End If

        'i = i + 1
    Next i

    'j = j + 1
Next j

End Sub

The data looks as follows :- Tables view

O is the offset between 3rd column from the left and 3rd column in the 3rd table in the image. The two loops are supposed to populate the table 3 using values in table 1. I have tried variations of Do While and While but that too gives the same problem. There is no error which is reported, just no output beyond the first column, once run. Can some one help me understand what is happening here or what am I missing here. Please let me know if I need to add more description for the issue.

Thanks a bunch !

Upvotes: 1

Views: 76

Answers (1)

Wizhi
Wizhi

Reputation: 6549

Since you need to define the For loop with a start and an end value:

For counter = start To end [Step increment]
   {...statements...}
Next [counter]

Changing this would solve your problem:

For j = 3 To j = 6 -> For j = 3 To 6

and

For i = 7 To i = 12 -> For i = 7 To 12

Upvotes: 1

Related Questions