Error with Loop

Jeeremy7

Board Regular
Joined
May 13, 2020
Messages
96
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a problem with one of the loop I'm trying to do and I think it's in the formula but could somoene help me ?
Also I saw that some macro run faster without Loop, is there a way to convert my code without loop ?

Here the code thanks !
VBA Code:
   'Put the total cost plus fee at the end of each code
    Dim A As Long
    Dim B As Integer
    A = ActiveCell.Offset(0, -2)
    B = Sheets("Notes").Range("B1")
    
    Range("K13").Select
    Do Until ActiveCell.Offset(0, -8) = "Total"
        If ActiveCell.Offset(1, -2) = "" Then
            ActiveCell = A * (1 + B)
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop
    
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. Windows
Can you help me as to what are you trying to do.
 

Jeeremy7

Board Regular
Joined
May 13, 2020
Messages
96
Office Version
  1. 365
Platform
  1. Windows
Can you help me as to what are you trying to do.
I'm trying to have totals and codes at the right place so formulas in other sheets can feed of those data sheets
Here's the shorter sheets for now:
1623882036292.png

And here's my code:
VBA Code:
Sub Format_Amico_Sage_Data()

   'Write "Total" at the end of the data
    Range("M1").End(xlDown).Select
    ActiveCell.Offset(0, -11).End(xlUp).Select
    ActiveCell.Offset(1, 1) = "Total"
    ActiveCell.Offset(1, 7) = "Total"
    
   'Copy paste code until next code
    Range("C7").Select
    Do Until ActiveCell = "Total"
        If ActiveCell.Offset(1, 0) = "" Then
            ActiveCell.Copy ActiveCell.Offset(1, 0)
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop
    
   'Put the total cost plus fee at the end of each code
    Dim A As Long
    Dim B As Integer
    A = ActiveCell.Offset(0, -2)
    B = Sheets("Notes").Range("B1")
    
    Range("K13").Select
    Do Until ActiveCell.Offset(0, -8) = "Total"
        If ActiveCell.Offset(1, -2) = "" Then
            ActiveCell = A * (1 + B)
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop
    
    
End Sub
 

Jeeremy7

Board Regular
Joined
May 13, 2020
Messages
96
Office Version
  1. 365
Platform
  1. Windows
Does anyone know why the second loop doesn't work ?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It would be helpful to first see a BEFORE sample (what your data looks like before running any macro), and your desired AFTER sample (what it should look like after running your macro).
Also, I think we need to know what is in cell B1 on your "Notes" sheet.

It would most helpful if you could use the "XL2BB" tool for posting your images, as that would show us all your formulas and allow us to easily copy/paste your data, so we can quickly recreate your scenario on our computers.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Jeeremy7

Board Regular
Joined
May 13, 2020
Messages
96
Office Version
  1. 365
Platform
  1. Windows
I thought my codes had good comment of all the steps I wanted to do.
I downloaded the add-in it's true that it's hard to work with a picture.. My bad !!
Thanks :) !

TEST - 1056 - Claim #1 - December 2020.xlsm
ABCDEFGHIJK
1
2TotalTotal
3
4DateDescriptionSourceJE#AmountCumulative
5XXXXXXXXXX
6REVENUE
74020XXXXXXXX
812-31-2020XXXXX1321J8697108,823.05108,823.05
9TOTAL REVENUE108,823.05
10
11EXPENSE
125103XXXXXXXXXXX
1312-20-2020XXXX20205686J9208316.69316.69
1412-27-2020XXXX20205805J9172192.27508.96
15508.96
165106XXXXXX
1711-23-2020XXX10230J8509764.851,273.81
1812-04-2020XXX10486J7904776.252,050.06
191,541.10
206997XXXXXXXXXXX
2111-06-2020XXX374644J7018157.5347,627.06
22TOTAL EXPENSE2,207.59
23
24REVENUE minus EXPENSE61,195.99
25Generated On: 06/16/2021
Test
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Is that the BEFORE or AFTER image?
It really would be most helpful to see both, so we can see that the data you are working with looks like, and what your desired result looks like.

Also, I think you missed this question in my previous post:
Also, I think we need to know what is in cell B1 on your "Notes" sheet.
 

Jeeremy7

Board Regular
Joined
May 13, 2020
Messages
96
Office Version
  1. 365
Platform
  1. Windows
That's the before here's the after:
The "Totals" I added at the end are not necessary it was just to try to make the loop works. Same for the codes like 5103 I just need the one on the same row as the amount with fee on column K to be able to run a sumifs on an other sheet.

Also the in note sheet is just a fee so it could be any %
TEST - 1056 - Claim #1 - December 2020.xlsm
ABCDEFGHIJK
1
2TotalTotal
3
4DateDescriptionSourceJE#AmountCumulative
5XXXXXXXXXX
6REVENUE
74020XXXXXXXX
8402012-31-2020XXXXX1321J8697108,823.05108,823.05
9TOTAL REVENUE4020108,823.05117,528.89
104020
11EXPENSE4020
125103XXXXXXXXXXX
13510312-20-2020XXXX20205686J9208316.69316.69
14510312-27-2020XXXX20205805J9172192.27508.96
155103508.96549.68
165106XXXXXX
17510611-23-2020XXX10230J8509764.851,273.81
18510612-04-2020XXX10486J7904776.252,050.06
1951061,541.101,664.39
206997XXXXXXXXXXX
21699711-06-2020XXX374644J7018157.5347,627.06
22TOTAL EXPENSE69972,207.592,384.20
23TotalTotal
24REVENUE minus EXPENSE61,195.99
25Generated On: 06/16/2021
Test
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Jeeremy()
   Dim Rng As Range
   
   For Each Rng In Range("J5", Range("J" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.Count, 1).Resize(1).FormulaR1C1 = "=rc[-2]*(1+Notes!r1c2)"
   Next Rng
End Sub
 

Jeeremy7

Board Regular
Joined
May 13, 2020
Messages
96
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Jeeremy()
   Dim Rng As Range
  
   For Each Rng In Range("J5", Range("J" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.Count, 1).Resize(1).FormulaR1C1 = "=rc[-2]*(1+Notes!r1c2)"
   Next Rng
End Sub
Hello Fluff and thanks I'll try that code in a couple hours :) !
I realized that you never use the LOOP system and your way is usually really faster. How is that and if there's no LOOP, what in the code is saying the macro to stop doing what it's doing and prevent it from crashing ?
 

Forum statistics

Threads
1,136,769
Messages
5,677,627
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top