Next Without For Error

mellymelle

New Member
Joined
Nov 25, 2016
Messages
45
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.

VBA Code:
        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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
What is value of IPLastRow? Dont see the variable assigned...
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,581
Office Version
2007
Platform
Windows
Change this

While rowNum <= auaAdjLastRow

By this

Loop While rowNum <= auaAdjLastRow
 

mellymelle

New Member
Joined
Nov 25, 2016
Messages
45
@ 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

Well-known Member
Joined
Dec 3, 2018
Messages
12,581
Office Version
2007
Platform
Windows

ADVERTISEMENT

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

mellymelle

New Member
Joined
Nov 25, 2016
Messages
45
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,581
Office Version
2007
Platform
Windows
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,304
Messages
5,510,503
Members
408,792
Latest member
S_s_s

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top