Error with Loop

[Jeeremy7]

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 !

   '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

 

[CA_Punit]

Can you help me as to what are you trying to do.

 

[Jeeremy7]

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:

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]

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]

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
	A	B	C	D	E	F	G	H	I	J	K
1
2			Total						Total
3
4					Date	Description	Source	JE#	Amount	Cumulative
5	XXXXXXXXXX
6		REVENUE
7			4020	XXXXXXXX
8					12-31-2020	XXXXX	1321	J8697	108,823.05	108,823.05
9		TOTAL REVENUE							108,823.05
10
11		EXPENSE
12			5103	XXXXXXXXXXX
13					12-20-2020	XXXX	20205686	J9208	316.69	316.69
14					12-27-2020	XXXX	20205805	J9172	192.27	508.96
15									508.96
16			5106	XXXXXX
17					11-23-2020	XXX	10230	J8509	764.85	1,273.81
18					12-04-2020	XXX	10486	J7904	776.25	2,050.06
19									1,541.10
20			6997	XXXXXXXXXXX
21					11-06-2020	XXX	374644	J7018	157.53	47,627.06
22		TOTAL EXPENSE							2,207.59
23
24	REVENUE minus EXPENSE								61,195.99
25	Generated 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]

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
	A	B	C	D	E	F	G	H	I	J	K
1
2			Total						Total
3
4					Date	Description	Source	JE#	Amount	Cumulative
5	XXXXXXXXXX
6		REVENUE
7			4020	XXXXXXXX
8			4020		12-31-2020	XXXXX	1321	J8697	108,823.05	108,823.05
9		TOTAL REVENUE	4020						108,823.05		117,528.89
10			4020
11		EXPENSE	4020
12			5103	XXXXXXXXXXX
13			5103		12-20-2020	XXXX	20205686	J9208	316.69	316.69
14			5103		12-27-2020	XXXX	20205805	J9172	192.27	508.96
15			5103						508.96		549.68
16			5106	XXXXXX
17			5106		11-23-2020	XXX	10230	J8509	764.85	1,273.81
18			5106		12-04-2020	XXX	10486	J7904	776.25	2,050.06
19			5106						1,541.10		1,664.39
20			6997	XXXXXXXXXXX
21			6997		11-06-2020	XXX	374644	J7018	157.53	47,627.06
22		TOTAL EXPENSE	6997						2,207.59		2,384.20
23			Total						Total
24	REVENUE minus EXPENSE								61,195.99
25	Generated On: 06/16/2021
Test

 

[Fluff]

How about

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]

How about

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 ?