Loop works on 1st cell in range but then stops...any suggestions?

EVANWIT84 · Nov 8, 2020

Hi All,

I am trying to loop thru each account in my account master, find the range of cells 7 rows over in the next tab, copy those cells into a calculation sheet with a multiple the values, then I want to paste the end result back into my account master sheet. I am able to get the 1st account to work in my list but then it stops after the 1st account and doesn't calculate any further. I'm thinking I need an outer loop where I state For Each Cell in AcctRng then let it loop thru the find, multiply, then copy and write a Next after. Any thoughts?

Sub FindAcctReturn()

'1. Store Accounts as Variable

Dim AcctNum As Long
Dim AcctRng As Range
Dim Cell As Range
Dim CompId As Range
Dim i As Byte
Dim FirstMatch As Variant
Dim CalcSheet As Worksheet
Dim h As Long
Dim lstCell As Long

Set CalcSheet = Sheet9
Set AcctRng = Sheet2.Range("A2:A1000")
Sheets("CalcSheet").Range("a1:i1000").ClearContents 'Clears Content in Calc Sheet Destination
'2 Lookup Accounts in Master Table

i = 3

Set CompId = Sheet1.Range("A:I").Find(AcctRng, LookIn:=xlValues, lookat:=xlWhole)

If Not CompId Is Nothing Then
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
FirstMatch = CompId.Address
Do
Set CompId = Sheet1.Range("A:A").FindNext(CompId)
If CompId.Address = FirstMatch Then Exit Do
i = i + 1
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value

Loop
End If

'3 Perform Calc of cumulative returns in CalcSheet by acct

lstCell = CalcSheet.Range("b1048567").End(xlUp).Row

For h = 2 To lstCell
If h = lstCell Then Exit Sub
CalcSheet.Cells(h, "c") = 1 + CalcSheet.Cells(h, "B")
CalcSheet.Range("C1").Value = "=PRODUCT(R[1]C:R[999]C)-1"
Sheet2.Range("D2").Value = CalcSheet.Range("C1").Value '4 Paste Data from Variable into Account Master Tab
Next

End Sub

maras · Nov 9, 2020

Perhaps something like that.

VBA Code:

Sub FindAcctReturn()

'1. Store Accounts as Variable

    Dim AcctNum As Long
    Dim AcctRng As Range
    Dim Cel As Range
    Dim CompId As Range
    Dim i As Byte
    Dim FirstMatch As Variant
    Dim CalcSheet As Worksheet
    Dim h As Long
    Dim lstCell As Long
    
    Set CalcSheet = Sheet9
    Set AcctRng = Sheet2.Range("A2:A1000")
    Sheets("CalcSheet").Range("a1:i1000").ClearContents 'Clears Content in Calc Sheet Destination
    '2 Lookup Accounts in Master Table
    
    i = 3
    For Each Cel In AcctRng
        Set CompId = Sheet1.Range("A:I").Find(Cel.Value, LookIn:=xlValues, lookat:=xlWhole)
        
        If Not CompId Is Nothing Then
            Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
            FirstMatch = CompId.Address
            Do
                Set CompId = Sheet1.Range("A:A").FindNext(CompId)
                If CompId.Address = FirstMatch Then Exit Do
                i = i + 1
                Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
            Loop
        End If
    Next
    
    '3 Perform Calc of cumulative returns in CalcSheet by acct
    
    lstCell = CalcSheet.Range("b1048567").End(xlUp).Row
    
    For h = 2 To lstCell
        If h = lstCell Then Exit Sub
        CalcSheet.Cells(h, "c") = 1 + CalcSheet.Cells(h, "B")
        CalcSheet.Range("C1").Value = "=PRODUCT(R[1]C:R[999]C)-1"
        Sheet2.Range("D2").Value = CalcSheet.Range("C1").Value '4 Paste Data from Variable into Account Master Tab
    Next

End Sub

EVANWIT84 · Nov 9, 2020

Hi maras,

Thanks for the assistance. It looks like my code makes it thru the first 172 rows of data then hits the Debug on i = i + 1. Overflow (Error 6).

As a result, I changed i from Byte to Long. Now, it looks like it copies all 70k rows from the one tab to the next when you suggest using the For Each Loop and then closing it after the End If statement. My order of operations needs to change somewhat. I need to only copy the data for 1 account at a time to the calc. sheet tab, calculate the total return, paste it, then begin the loop again. Let me see if I can close the loop later on and perhaps that may work. I will circle back.

Thanks,
Evan

EVANWIT84 · Nov 9, 2020

EVANWIT84 said:
Hi maras,

Thanks for the assistance. It looks like my code makes it thru the first 172 rows of data then hits the Debug on i = i + 1. Overflow (Error 6).

As a result, I changed i from Byte to Long. Now, it looks like it copies all 70k rows from the one tab to the next when you suggest using the For Each Loop and then closing it after the End If statement. My order of operations needs to change somewhat. I need to only copy the data for 1 account at a time to the calc. sheet tab, calculate the total return, paste it, then begin the loop again. Let me see if I can close the loop later on and perhaps that may work. I will circle back.

Thanks,
Evan

I tried nesting the loop outside to no avail. Open to any other suggestions.

Sub FindAcctReturnv3()

'1. Store Accounts as Variable

Dim AcctNum As Long
Dim AcctRng As Range
Dim Cel As Range
Dim CompId As Range
Dim i As Long
Dim FirstMatch As Variant
Dim CalcSheet As Worksheet
Dim h As Long
Dim lstCell As Long

Set CalcSheet = Sheet9
Set AcctRng = Sheet2.Range("A2:A1000")
Sheets("CalcSheet").Range("a1:i1000").ClearContents 'Clears Content in Calc Sheet Destination
'2 Lookup Accounts in Master Table
lstCell = CalcSheet.Range("b1048567").End(xlUp).Row
i = 3

For Each Cel In AcctRng
Set CompId = Sheet1.Range("A:I").Find(Cel.Value, LookIn:=xlValues, lookat:=xlWhole)

If Not CompId Is Nothing Then
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
FirstMatch = CompId.Address
Do
Set CompId = Sheet1.Range("A:A").FindNext(CompId)
If CompId.Address = FirstMatch Then Exit Do
i = i + 1
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
Loop
For h = 2 To lstCell
CalcSheet.Cells(h, "c") = 1 + CalcSheet.Cells(h, "B")
CalcSheet.Range("C1").Value = "=PRODUCT(R[1]C:R[999]C)-1"
Sheet2.Range("D2").Value = CalcSheet.Range("C1").Value '4 Paste Data from Variable into Account Master Tab
Next

End If

Sheet2.Range("D2").Value = Sheet2.Range("D2").Offset(1, 0)

Next

End Sub

GWteB · Nov 9, 2020

You might consider providing a sample of the four, perhaps five, worksheets involved.
Use XL2BB for that and explain clearly what exactly you expect from the code.

Finally, please paste your code between code tags:

EVANWIT84 · Nov 9, 2020

EVANWIT84 said:
Hi All,

I am trying to loop thru each account in my account master, find the range of cells 7 rows over in the next tab, copy those cells into a calculation sheet with a multiple the values, then I want to paste the end result back into my account master sheet. I am able to get the 1st account to work in my list but then it stops after the 1st account and doesn't calculate any further. I'm thinking I need an outer loop where I state For Each Cell in AcctRng then let it loop thru the find, multiply, then copy and write a Next after. Any thoughts?

Sub FindAcctReturn()

'1. Store Accounts as Variable

Dim AcctNum As Long
Dim AcctRng As Range
Dim Cell As Range
Dim CompId As Range
Dim i As Byte
Dim FirstMatch As Variant
Dim CalcSheet As Worksheet
Dim h As Long
Dim lstCell As Long

Set CalcSheet = Sheet9
Set AcctRng = Sheet2.Range("A2:A1000")
Sheets("CalcSheet").Range("a1:i1000").ClearContents 'Clears Content in Calc Sheet Destination
'2 Lookup Accounts in Master Table

i = 3

Set CompId = Sheet1.Range("A:I").Find(AcctRng, LookIn:=xlValues, lookat:=xlWhole)

If Not CompId Is Nothing Then
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
FirstMatch = CompId.Address
Do
Set CompId = Sheet1.Range("A:A").FindNext(CompId)
If CompId.Address = FirstMatch Then Exit Do
i = i + 1
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value

Loop
End If

'3 Perform Calc of cumulative returns in CalcSheet by acct

lstCell = CalcSheet.Range("b1048567").End(xlUp).Row

For h = 2 To lstCell
If h = lstCell Then Exit Sub
CalcSheet.Cells(h, "c") = 1 + CalcSheet.Cells(h, "B")
CalcSheet.Range("C1").Value = "=PRODUCT(R[1]C:R[999]C)-1"
Sheet2.Range("D2").Value = CalcSheet.Range("C1").Value '4 Paste Data from Variable into Account Master Tab
Next

End Sub

Here's the 1st tab of the account master:

Client Account Number	Client Master Account Name	Clent Account Name	Cumulative Return
1	Ev	Ev
2	R	R
3	Donald	Donald
4	Biden	Biden

Here's the 2nd tab with the data:

Client Account Number	Client Master Account Name	Clent Account Name	Period	Contribution	Withdrawal	Ending Market Value	Net Return
1						1	0.0181
1						1	0.031
1						1	0.0313
1						1	0.0555
1						1	0.0435
1						1	-0.0082
1						1	-0.0156
1						1	0.176
1						1	0.0613
1						1	0.0222
1						1	0.0686
1						1	0.0747
1						1	0.0572
1						1	0.0775
1						1	0.0822
1						1	0.061
1						1	0.0904
1						1	0.0381
1						1	0.0867
1						1	0.0511
1						1	0.0236
1						1	0.0754
1						1	0.1482
1						1	0.1957
1						1	0.1322
1						1	0.1819
1						1	0.2059
1						1	0.1299
1						1	0.1424
1						1	0.108
1						1	0.2096
1						1	0.1413
1						1	0.1998
1						1	0.2443
1						1	0.2
1						1	0.2566
1						1	0.268
1						1	0.1355
1						1	0.2564
1						1	0.1389
1						1	0.202
1						1	0.1101
1						1	0.1462
1						1	0.1546
1						1	0.1705
1						1	0.0463
1						1	0.0834
1						1	0.0427
1						1	0.0247
1						1	0.0024
1						1	-0.0108
2						1	0.0147
3						1	-0.3733

GWteB · Nov 9, 2020

GWteB said:
... and explain clearly what exactly you expect from the code.

EVANWIT84 · Nov 9, 2020

Good point. I will try to be clear but apologies if it doesn't make sense. Long story short is that the code does not perform the routine over and over across all accounts.

Variables & Objects

Dim AcctNum As Long
Dim AcctRng As Range
Dim Cel As Range
Dim CompId As Range
Dim i As Long
Dim FirstMatch As Variant
Dim CalcSheet As Worksheet
Dim h As Long
Dim lstCell As Long

Set CalcSheet = Sheet9
Set AcctRng = Sheet2.Range("A2:A1000")
Sheets("CalcSheet").Range("a1:i1000").ClearContents 'Clears Content in Calc Sheet Destination

'’’’’’’ I want to Lookup Accounts in Master Table.’’’’’’’’’’’’’’’’’’
lstCell = CalcSheet.Range("b1048567").End(xlUp).Row
i = 3


Client Account Number	Client Master Account Name	Clent Account Name	Cumulative Return
1	Ev	Ev
2	R	R
3	Donald	Donald
4	Biden	Biden

‘’’’’’’On the next 9 lines here I am looking up the accounts and finding in the Raw Data table. I am then taking the raw data, 7 columns over and pasting into the Calc.Sheet. This code is working as expected.’’’’’’’

Set CompId = Sheet1.Range("A:I").Find(AcctRng, LookIn:=xlValues, lookat:=xlWhole)

If Not CompId Is Nothing Then
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value
FirstMatch = CompId.Address
Do
Set CompId = Sheet1.Range("A:A").FindNext(CompId)
If CompId.Address = FirstMatch Then Exit Do
i = i + 1
Sheets("CalcSheet").Range("B" & i).Value = CompId.Offset(, 7).Value

Loop
End If
Here's the 2nd tab with the data:

Client Account Number	Client Master Account Name	Clent Account Name	Period	Contribution	Withdrawal	Ending Market Value	Net Return
1						1	0.0181
1						1	0.031
1						1	0.0313
1						1	0.0555

‘’’’’’’’’'3 Perform Calc of cumulative returns in CalcSheet by acct. This codes works and calculates the product of the results from the prior code. This is just a blank worksheet.

lstCell = CalcSheet.Range("b1048567").End(xlUp).Row

For h = 2 To lstCell
If h = lstCell Then Exit Sub
CalcSheet.Cells(h, "c") = 1 + CalcSheet.Cells(h, "B")
CalcSheet.Range("C1").Value = "=PRODUCT(R[1]C:R[999]C)-1"
Sheet2.Range("D2").Value = CalcSheet.Range("C1").Value '4 Paste Data from Variable into Account Master Tab
Next
‘’’’’’’’’’’’At this point I would like the routine to start back over and loop thru again to calculate. But it stops here.
End Sub

maras · Nov 10, 2020

It's hard to understand your problem.
Maybe that's it.
1st tab (final) is on sheet 2 and tab 2 (with data) on sheet 1.
Run the code and check.

VBA Code:

Sub maras()
    Dim a(), af
    Dim i As Long, r As Long
    
    With Sheets("Sheet1")
        a = .[A1].CurrentRegion.Value
        ReDim af(1 To UBound(a), 1 To 4)
        
        For i = 2 To UBound(a)
            If a(i, 1) <> a(i - 1, 1) Then
                r = r + 1
                af(r, 1) = a(i, 1)
                af(r, 2) = a(i, 2)
                af(r, 3) = a(i, 3)
            End If
            af(r, 4) = af(r, 4) + a(i, 8)
        Next
    End With
    
    With Sheets("Sheet2")
        .UsedRange.ClearContents
        With .[A1]
            .Resize(, 4) = [{"Client Account Number","Client Master Account Name", "Client Account Name","Cumulative Return"}]
            .Offset(1).Resize(r, UBound(af, 2)) = af
        End With
    End With
End Sub

GWteB · Nov 10, 2020

maras said:
It's hard to understand your problem.

Agree on that.

Loop works on 1st cell in range but then stops...any suggestions?

EVANWIT84

New Member

Excel Facts

maras

New Member

EVANWIT84

New Member

EVANWIT84

New Member

GWteB

Well-known Member

EVANWIT84

New Member

GWteB

Well-known Member

EVANWIT84

New Member

maras

New Member

GWteB

Well-known Member

Similar threads

Forum statistics

Share this page

Loop works on 1st cell in range but then stops...any suggestions?

New Member

Excel Facts

New Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock