Next Without For Error

[mellymelle]

Hoping this just needs another set of eyes (ones better than mine). Getting Next without For error, verified that there is a For for every Next and vice versa via a word search, and embedded loops are contained entirely within the outer loop.

The code is supposed to go though a list, find all instances of a certain value and pull data based on the location of the searched value. Below is the partial code - it is the Next in the 2nd For loop that is generating the error, any suggestions are appreciated. Thanks in advance.

        Dim i As Long
        Dim auaAdjLastRow As Long
        Dim acct As String
    
        auaAdjLastRow = Sheet2.Cells(Rows.Count, "T").End(xlUp).Row
        For i = 2 To auaAdjLastRow
            acct = Sheet2.Range("T" & i).Value2
            Sheet2.Range("T" & i).Formula = Left(acct, 3) & Mid(acct, 5, 4) & Right(acct, 1)
        Next i

        Range("Q1").Formula = "Adjusted AUA"
        
        Dim rng As Range
        Dim rowNum As Long
        Dim j As Long
        Dim adjTotal As Single
        
        For j = 2 To IPLastRow
            Set rng = Sheet2.Range("T2:T" & auaAdjLastRow)
            acct = Sheet1.Range("A" & j).Value2
            adjTotal = 0
            Do
                rowNum = Application.Match(acct, rng, 0)
                If Not IsError(rowNum) Then
                    adjTotal = adjTotal + Sheet2.Range("V" & rowNum).Value2
                    Set rng = Sheet2.Range("T" & rowNum + 1 & ":T" & auaAdjLastRow)
                Else
                    rowNum = auaAdjLastRow + 1
                End If
            While rowNum <= auaAdjLastRow
        Next j

 

[jim may]

What is value of IPLastRow? Dont see the variable assigned...

 

[DanteAmor]

Change this

While rowNum <= auaAdjLastRow

By this

Loop While rowNum <= auaAdjLastRow

 

[mellymelle]

@ Jim - IPLastRow is also assigned via a Rows.Count method. It is a valid value though - I'm adding an update to an existing code that functioned with that variable prior to the update.

@ DanteAmor - thank you! I rarely use the do while loop and should've reviewed the syntax instead of relying on my memory. How embarrassing.

Update: made Dante's suggestion but now there's another error elsewhere. Leaving the office though and will get back to it tomorrow, if you're interested in knowing what other issues came up, let me know and I'll make an update once I work through the other errors (or ask more questions in a new thread if I can't solve them).

Thanks both!

 

[DanteAmor]

Of course, if you explain what you need with some examples, your code may be optimized.
Glad to help you.

 

[mellymelle]

This is just an update if still interested. This is the gist of the mandate: have a table with accounts & balance on one sheet (each account is listed only once), and on another sheet another table with accounts and adjustment values (accounts can be listed more than once here with different adjustment values for different holdings).

I wanted to learn how to use match (yes this is how green I am) which is why I chose the initial method above, it essentially loops each account listing, and then matches the first record of the same account on the 2nd worksheet and adds the adjustment value to the adjTotal variable. Then the range to match in is reset to the rest of the dataset not yet reviewed and will keep looping this way until it reaches the end of the dataset. Then it will start the whole process for the next account in the 1st data set until all accounts have been reviewed.

After applying Dante's fix then debugging a few other errors, found myself in an infinite loop, which is likely due to an error in how I'm reassigning the range within the loop (the code was different from above after fixes). That's when I went home last night.

Decided to scrap the whole thing today and instead went with - create a pivot table of the 2nd data set to generate a list where each account is listed only once, then use a simple vlookup formula to adjust the balance in the first data set.

I probably sound like a garbage fire here, thus any critique/suggestion is welcome!

 

[DanteAmor]

Sorry, but I don't understand what you need.
To better understand what you need, you can help with examples of your data and the results you require.
Use XL2BB tool, see my signature.