Formula changing within a loop and paste

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
421
Office Version
  1. 2019
Platform
  1. Windows
Thanks in advance and I will let you know if the assistance/recommendation works.

I have a loop that goes from the second to the last row.

In one column I have a counter that works (Cells(n, 7) = n - 1) as it goes from 2 to the lastrow.

I also want to calculate the following formulas and paste special values after they calculate. I know you can do this without a loop, but there are some other items I'm also doing in the loop so I would like to keep the loop. In your recommendation, please give one where I can use the loop.

Also, note that I am combining some text and entries of cells so that seems to be an issue.

Column H
=G2&". "&C2

Column I
="Document No.: "&D2

Column J
="Amount: "&TEXT(E2,"$#,##0")

Column M
="ren "&""""&G2&""""&" "&""""&G2&". "&C2&" - "&D2&" - "&TEXT(E2,"$#,##0")&""""

This is the code for my loop. I just need the code for these three lines and yes there is a reason to have the "" after executed (i.e. I want the test to be in quotes where indicated such as "Text").

Thanks!

Code:
Sub Formulas()
               Dim n as Integer
               Dim LastRow12 as Long
                
                For n = 2 To LastRow12
                
                    Cells(n, 7) = n - 1
                
                
                Next n

End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try this:
Code:
Sub Formulas()
               Dim n As Integer
               Dim LastRow12 As Long
                
                For n = 2 To LastRow12
                
                    Cells(n, 7) = n - 1
                    Cells(n, "H") = Cells(n, "G") & ". " & Cells(n, "C")
                    Cells(n, "J") = "Amount: " & Format(Cells(n, "E"), "$#,##0")
                    Cells(n, "M") = "ren " & """" & Cells(n, "G") & """" & " " & """" & Cells(n, "G") & ". " & _
                                    Cells(n, "C") & " - " & Cells(n, "D") & " - " & Format(Cells(n, "E"), "$#,##0") & """"
                
                Next n

End Sub
Note: I see one potential issue though. Where are you calculating LastRow12?
 
Upvote 0
Thanks. I will try this out and let you know once I finish up the rest to let you know if it works.
I actually already have it calculated which brings me to my next question.

Once I declare, for example:
Dim i as Integer
Dim LastRow as Long

and calculate the LastRow as such can I:


(1) Reuse the integer I in another loop?
(2) Reuse the LastRow dimension to find the lastrow of another spreadsheet.

Basically when do you have to declare new dimensions/variables?

I can give you a more detailed example if needed.
 
Upvote 0
You usually declare variables at the top of your Procedures and Functions.
You can re-use them for different purposes within the same procedure if you like, but I would recommend against it (not worth the confusion and potential issues they may cause if you are not careful).
 
Upvote 0
Try this:
Code:
Sub Formulas()
               Dim n As Integer
               Dim LastRow12 As Long
              
                For n = 2 To LastRow12
              
                    Cells(n, 7) = n - 1
                    Cells(n, "H") = Cells(n, "G") & ". " & Cells(n, "C")
                    Cells(n, "J") = "Amount: " & Format(Cells(n, "E"), "$#,##0")
                    Cells(n, "M") = "ren " & """" & Cells(n, "G") & """" & " " & """" & Cells(n, "G") & ". " & _
                                    Cells(n, "C") & " - " & Cells(n, "D") & " - " & Format(Cells(n, "E"), "$#,##0") & """"
              
                Next n

End Sub
Note: I see one potential issue though. Where are you calculating LastRow12?
Late Response, but I'm trying to go through all my questions and collect ones that I can store and use in the future if I do not have the method down yet. I also want to list which response works. I added how to calculate last row to the code.



VBA Code:
Sub Formulas()
               Dim n As Integer
               Dim LastRow12 As Long

               LastRow12 = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, _
                    LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
               
                For n = 2 To LastRow12
               
                    Cells(n, 7) = n - 1
                    Cells(n, "H") = Cells(n, "G") & ". " & Cells(n, "C")
                    Cells(n, "J") = "Amount: " & Format(Cells(n, "E"), "$#,##0")
                    Cells(n, "M") = "ren " & """" & Cells(n, "G") & """" & " " & """" & Cells(n, "G") & ". " & _
                                    Cells(n, "C") & " - " & Cells(n, "D") & " - " & Format(Cells(n, "E"), "$#,##0") & """"
               
                Next n

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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
Back
Top