# Error with Loop

#### Jeeremy7

##### Board Regular
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
Can you help me as to what are you trying to do.

#### Jeeremy7

##### Board Regular
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:

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
Does anyone know why the second loop doesn't work ?

#### Joe4

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
I thought my codes had good comment of all the steps I wanted to do.
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

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
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
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
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 ?

Replies
7
Views
55
Replies
3
Views
177
Replies
3
Views
100
Replies
3
Views
291
Replies
4
Views
133

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.

### Which adblocker are you using?

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

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