Formula changing within a loop and paste

[OilEconomist]

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!

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

End Sub

 

[Joe4]

Try this:

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?

 

[OilEconomist]

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.

 

[Joe4]

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).

 

[OilEconomist]

Try this:

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.

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