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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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,102
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,102
Office Version
2007
Platform
Windows
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,102
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,934
Messages
5,483,769
Members
407,409
Latest member
natshafernandes

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top