LOOPING PROBLEM

blackorchids2002

Board Regular
Joined
Dec 29, 2011
Messages
135
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,655
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Board Regular
Joined
Dec 29, 2011
Messages
135
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

Board Regular
Joined
Dec 29, 2011
Messages
135
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,118
Messages
5,546,040
Members
410,722
Latest member
Jojo3008
Top