Help! Need to repeat macro for all rows

[kaleonard08]

I am very basic at macros and I need some help.
I recorded the below macro and I need it to repeat but for all the rows. This was just using the record macro button so I did not do all the coding.

A little explanation: I have a table that is a calculator. I have a project that has a range of 3,000 to 5,000 rows that I would need to input the quantity and cost into the calculator. The table is on the "quote" worksheet and has three columns (qty, cost, and resale). I need to input the qty and cost into the calculator on a separate worksheet ("unit resale calculator") which will calculate the resale. Once I have the resale from the calculator, I want to input that back into the third column (resale) on the "quote" worksheet. As mentioned earlier, I recorded the first row in a macro (see below) but I need this macro to keep repeating until all the rows are complete which is an unknown number of times. Hope this is enough information. If additional information is needed, please let me know.


Sub Calculate()
'
' Calculate Macro
'

'
Sheets("Unit Resale Calculator").Select
Range("E9").Select
ActiveCell.FormulaR1C1 = "=Quote!R[-7]C[-4]"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=Quote!R[-8]C[-3]"
Range("E18").Select
Sheets("Quote").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "='Unit Resale Calculator'!R[16]C[2]"
Range("C3").Select

End Sub

 

[Captain_Conman]

How does your calculator work? Is it a function or VBA doing the calculation? Could you please post it?

 

[jolivanes]

Do you want to copy cell A2 from Sheet("Quote") into cell E9 of Sheet("Unit Resale Calculator")?
and copy cell B2 from Sheet("Quote") into cell E10 of Sheet("Unit Resale Calculator")?
and then which cell from Sheet("Unit Resale Calculator") needs to be copied into which cell (C2 I assume) of Sheet("Quote")?
Now explain how you want to proceed with this.
Just let us know which ranges from which sheets you want copied to where in what sheet.

 

[kaleonard08]

I am not sure how my calculator works. It is pre-programmed.
"Unit Resale Calculator" worksheet - E9 is the Quantity, E10 is the Unit Cost, E18 is the Unit Resale.
"Quote" worksheet - 3 columns - A is Quantity column, B is Cost column, and C is the resale column.

What I need is to copy the quantity from "Quote" worksheet and paste into the calculator (E9) and hit enter. Then, I need to copy the cost from the "Quote" worksheet and past into the calculator (E10) and hit enter. This will automatically calculate the resale E18. Then, I need to copy the resale from the calculator and paste into the resale column on "Quote" worksheet.

The "Quote" worksheet will be populated with an unknown number of rows. I need the macro to repeat until all the rows are complete.

Also, I need to skip or highlight an empty cell. If there is an error in the table, I don't want to have to stop. I want it to keep going.

Hope this helps! Let me know if you still have questions.


THANK YOU!!

 

[jolivanes]

Looks like you should put the formula from E18 in the resale column.

 

[jolivanes]

Sub Maybe()
Dim sh1 As Worksheet, sh2 As Worksheet, i As Long
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
    For i = 2 To sh1.Cells(Rows.Count, 1).End(xlUp).Row
        If Len(sh1.Cells(i, 1)) <> 0 Then
            sh2.Cells(9, 5).Value = sh1.Cells(i, 1).Value
            sh2.Cells(10, 5).Value = sh1.Cells(i, 2).Value
            sh1.Cells(i, 3).Value = sh2.Cells(18, 5).Value
        End If
        sh2.Range("E9:E10").ClearContents
    Next i
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

In Post #6 , you need to change "Sheet1" to "Quote" and "Sheet2" to "Unit Resale Calculator"

 

[kaleonard08]

This worked! Thank you for your help!

I do have one more question. Is there a way to to make this copy up to 4 decimal places? When I run this macro, it rounds up to the 2 decimal places (example: 12.86). I need this to copy up to 4 decimal places (example: 12.8641).

Thanks!!!

 

[jolivanes]

Format the cells to show four decimals is the best way.

 

[kaleonard08]

Hi There!

When I do that, it just gives me 0's at the end instead of the actual number. I just wasn't sure if there was another way to do it.


Thanks!