LOOPING PROBLEM

[blackorchids2002]

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

1. I want to do a loop from the data in Sheet 1 (mmraw) until the last record
2. In Sheet 2 (mmcalc), create a reference formula for the data in Sheet 1
3. In Sheet 2 create a fomula in columns T to U.
4. 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.

I am actually stopped on the syntax to use a cells.formula below
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

 

[jolivanes]

Is this one of the culprits? It does not need "Range" I Think. "Range(Cells(5 + x, 14)"

If you do the following, you can check if your formula in the code is right.
Enter the formula in a cell
Select the cell
Tools, Macro, Record
F2, Enter
Go and look at the macro.

 

[blackorchids2002]

Is this one of the culprits? It does not need "Range" I Think. "Range(Cells(5 + x, 14)"

If you do the following, you can check if your formula in the code is right.
Enter the formula in a cell
Select the cell
Tools, Macro, Record
F2, Enter
Go and look at the macro.

I was trying to replace it with that range code from the original code below

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

 

[blackorchids2002]

I was trying to replace it with that range code from the original code below

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

I got it..

Cells(4 + x, 20).Formula = "=" & "N" & 4 + x

 

[blackorchids2002]

The code below is nearly done except if the discount does not equal to zero then it will copy the row with data and insert the copied cells on the next row.

IF the condition is met, see the below code:
If Cells(4 + x, 22) <> 0 Then ' IF THE DISCOUNT COLUMN DOES NOT EQUAL TO ZERO THEN COPY THE ROW AND INSERT COPIED CELLS
Rows(4 + x).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Cells(5 + x, 10).Value = "PROMOS AND DISCOUNTS"
Cells(5 + x, 14).Value = Cells(5 + x, 22)
Cells(5 + x, 16).Value = Cells(5 + x, 14) * Cells(5 + x, 15)
End If

Then, it should loop again to the next record from Sheet 1 and record the data in Sheet 2 and the data should be recorded on the next available row in Sheet 2.
For x = 2 To Last_Row
If mmraw.Cells(x - 1, 1).Value <> "" Then
mmcalc.Activate
With ActiveSheet
For y = 1 To 17 ' LOOP THROUGH COLUMNS TO COPY THE DATA FROM SHEET MMRAW
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).Formula = "=" & "N" & 4 + x
Cells(4 + x, 21).Formula = "=" & "P" & 4 + x & "/" & "O" & 4 + x
Cells(4 + x, 22).Formula = "=" & "U" & 4 + x & "-" & "T" & 4 + x
Next
End With
ElseIf x = "" Then
Cells(5, 1).Select
End If
Next

See below image

Full Code:
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 ' LOOP THROUGH COLUMNS TO COPY THE DATA FROM SHEET MMRAW
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).Formula = "=" & "N" & 4 + x
Cells(4 + x, 21).Formula = "=" & "P" & 4 + x & "/" & "O" & 4 + x
Cells(4 + x, 22).Formula = "=" & "U" & 4 + x & "-" & "T" & 4 + x
Next
End With
ElseIf x = "" Then
Cells(5, 1).Select
End If

If Cells(4 + x, 22) <> 0 Then ' IF THE DISCOUNT COLUMN DOES NOT EQUAL TO ZERO THEN COPY THE ROW AND INSERT COPIED CELLS
Rows(4 + x).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Cells(5 + x, 10).Value = "PROMOS AND DISCOUNTS"
Cells(5 + x, 14).Value = Cells(5 + x, 22)
Cells(5 + x, 16).Value = Cells(5 + x, 14) * Cells(5 + x, 15)

End If

Next
End With
End Sub