blackorchids2002
Board Regular
- Joined
- Dec 29, 2011
- Messages
- 138
Hi Masters,
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).
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
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