#### blackorchids2002

##### Board Regular

- Joined
- Dec 29, 2011

- Messages
- 135

It's been a while that I am not doing macros but for now I need some help again.

What I am trying to do?

Sheet 1 = mmraw (this the raw data)

Sheet 2 = mmcalc (this is where I need to change some data from Sheet 1 and where I need to put some formulas in other columns).

- I want to do a loop from the data in Sheet 1 (mmraw) until the last record
- In Sheet 2 (mmcalc), create a reference formula for the data in Sheet 1
- In Sheet 2 create a fomula in columns T to U.
- For each record In Sheet 2, if Discount column (V) does not equal to zero, I will copy the row above and put the discount amount in column N.

Example code below that is not working properly as formula:

'Cells(4 + x, 20).FormulaR1C1 = "=" & Cells(5 + x, 14)

'Cells(4 + x, 21).FormulaR1C1 = "=" & Cells(5 + x, 16) & "/" & Cells(5 + x, 15)

'Cells(4 + x, 22).FormulaR1C1 = "=" & Cells(5 + x, 21) & "-" & Cells(5 + x, 20)

Dim Last_Row As Long

Dim mmraw As Worksheet, mmcalc As Worksheet

Dim x As Integer, y As Integer

Set mmraw = Sheets("Excel Report")

Set mmcalc = Sheets("WP-Excel Report")

mmraw.Activate

mmraw.Cells(1, 1).Select

With ActiveSheet

Last_Row = Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To Last_Row

If mmraw.Cells(x - 1, 1).Value <> "" Then

mmcalc.Activate

With ActiveSheet

For y = 1 To 17

Cells(4 + x, 1).Value = x - 1 'ROW A6 IN WP-Excel Report

Cells(4 + x, y + 1).FormulaR1C1 = mmraw.Cells(x, y) 'ROW 2 & COL 1 IN Excel Report

'Cells(4 + x, 20).FormulaR1C1 = "=" & Range(Cells(5 + x, 14))

'Cells(4 + x, 21).FormulaR1C1 = "=" & mmraw.Cells(5 + x, 16) & "/" & Cells(5 + x, 15)

'Cells(4 + x, 22).FormulaR1C1 = "=" & mmraw.Cells(5 + x, 21) & "-" & Cells(5 + x, 20)

Next

End With

ElseIf x = "" Then

Cells(5, 1).Select

End If

Next

End With

End Sub