# Formula changing within a loop and paste

#### OilEconomist

##### Active Member
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Joe4

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?

#### OilEconomist

##### Active Member
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

##### Active Member
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``````

Replies
6
Views
421
Replies
2
Views
125
Replies
10
Views
365
Replies
1
Views
384
Replies
3
Views
134

### Forum statistics

1,190,655
Messages
5,982,131
Members
439,757
Latest member
85Sarah2005 ### 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