VBA column identifier and looping
 

I require assistance for the following code for (a) how to state all €œAO€� to
be relative and (b) for the code to execute Cols AO AP AQ (skip next 3 cols)
then cols AU AV AW (skip next 3 cols) and so on until Cols BY BZ C(ie 7
times).
As always Thank you in advance

Sub SproductVBA()

Dim i As Long
For i = 4 To 387 Step 32
Cells(i, "AO").Resize(31).FormulaR1C1 = _
"=SUMPRODUCT(--(R4C1:R3000C1=RC13),--(R4C5:R3000C5=R2C41),--(R4C7:R3000C7=R3C41),R4C6:R3000C6)"
Cells(i + 31, "AO").FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"

Next i
Range("AO4:AO387").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

--
Robert

VBA column identifier and looping
 

Hopefully

Sub SproductVBA()
Dim i As Long
Dim j As Long

With Application
.ScreenUpdating = False
End With

For j = 41 To 79 Step 6
For i = 4 To 387 Step 32
Cells(i, j).Resize(31).FormulaR1C1 = _
"=SUMPRODUCT(--(R4C1:R3000C1=RC13),--(R4C5:R3000C5=R2C)," & _
"--(R4C7:R3000C7=R3C),R4C6:R3000C6)"
Cells(i + 31, j).FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"

Next i
Cells(4, j).Resize(384).Copy Cells(4, j).Resize(384, 3)
Cells(4, j).Resize(384, 3).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next j

With Application
.ScreenUpdating = True
End With

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Robert" wrote in message
...
I require assistance for the following code for (a) how to state all "AO"
to
be relative and (b) for the code to execute Cols AO AP AQ (skip next 3
cols)
then cols AU AV AW (skip next 3 cols) and so on until Cols BY BZ C(ie 7
times).
As always Thank you in advance

Sub SproductVBA()

Dim i As Long
For i = 4 To 387 Step 32
Cells(i, "AO").Resize(31).FormulaR1C1 = _

"=SUMPRODUCT(--(R4C1:R3000C1=RC13),--(R4C5:R3000C5=R2C41),--(R4C7:R3000C7=R3
C41),R4C6:R3000C6)"
Cells(i + 31, "AO").FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"

Next i
Range("AO4:AO387").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

--
Robert

VBA column identifier and looping
 

Bob, Thanks for responding (I had hunch it will be you will). I seem to have
some problems. I had to add line 6, else all data will be misplaced. I also
had to change
"RC13" to "RC32" which is a new column of dates with break every 31 days
uniformly irrespective of the number of days in the month for the "TOTAL". The
errors now are (a) The copy paste special. Only cols 77-79 (BY-CA) is being
copied to cols 42-43 (incorrect) while the process is not perfomed for the
rest of the columns. I have tried with break since your post. If you could
once again, I will
appreciate it. Bye for now until tomorrow. The new code is produced below.

Sub SproductVBANew()
Dim i As Long
Dim j As Long

With Application
..ScreenUpdating = False
End With
Application.Goto Reference:="R4C41" '******
For j = 41 To 79 Step 6
For i = 4 To 387 Step 32
Cells(i, j).Resize(31).FormulaR1C1 = _
"=SUMPRODUCT(--(R4C1:R3000C1=RC32),--(R4C5:R3000C5=R2C),--(R4C7:R3000C7=R3C),R4C6:R3000C6)"

Cells(i + 31, j).FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"

Next i
'Application.Goto Reference:="R4C41"
Cells(4, j).Resize(384).Copy Cells(4, j).Resize(384, 3)
Cells(4, j).Resize(384, 3).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next j

With Application
..ScreenUpdating = True
End With

End Sub
--
Robert


"Bob Phillips" wrote:

Hopefully

Sub SproductVBA()
Dim i As Long
Dim j As Long

With Application
.ScreenUpdating = False
End With

For j = 41 To 79 Step 6
For i = 4 To 387 Step 32
Cells(i, j).Resize(31).FormulaR1C1 = _
"=SUMPRODUCT(--(R4C1:R3000C1=RC13),--(R4C5:R3000C5=R2C)," & _
"--(R4C7:R3000C7=R3C),R4C6:R3000C6)"
Cells(i + 31, j).FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"

Next i
Cells(4, j).Resize(384).Copy Cells(4, j).Resize(384, 3)
Cells(4, j).Resize(384, 3).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Next j

With Application
.ScreenUpdating = True
End With

End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Robert" wrote in message
...
I require assistance for the following code for (a) how to state all "AO"
to
be relative and (b) for the code to execute Cols AO AP AQ (skip next 3
cols)
then cols AU AV AW (skip next 3 cols) and so on until Cols BY BZ C(ie 7
times).
As always Thank you in advance

Sub SproductVBA()

Dim i As Long
For i = 4 To 387 Step 32
Cells(i, "AO").Resize(31).FormulaR1C1 = _

"=SUMPRODUCT(--(R4C1:R3000C1=RC13),--(R4C5:R3000C5=R2C41),--(R4C7:R3000C7=R3
C41),R4C6:R3000C6)"
Cells(i + 31, "AO").FormulaR1C1 = "=SUM(R[-31]C:R[-1]C)"

Next i
Range("AO4:AO387").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

--
Robert