LOOPING PROBLEM

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




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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.
 
Upvote 0
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))
 
Upvote 0
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
1601365461309.png



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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top