msquared99
06-14-2012, 06:51 AM
OK, I'm a bit bumfuzzled here. I am new at this. Here is what I have. In coulmns O thru Z are total sales for each month by rep. The number of rows can change (be more or less). My data will always start in cell O2 thru Z2, but I do not know which row it will end with. Columns A thru K contain the raw data and the below macro is summing it up.
I want to be able to add a sum for each month at the bottom of the data. This month it may be row 27, next month it may be row 30 or 25.
Here is the line causing me the issue:
Range("N" & EndRow + 1 : "Z" & EndRow +1).FormulaR1C1 = "=Sum(R"[EndRow +1]"C:R[-1])"
I get a Compile Error Expected: List separator
All I want is to add a sum to each known column but to a variable row in columns O thru Z.
Thanks,
Here is the entire macro:
Sub RepsRevenue()
Dim IRange As Range
Dim Orange As Range
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
nextcol = Cells(1, Columns.Count).End(xlToLeft).Column + 4
Range("H1").Copy Destination:=Cells(1, nextcol)
Set Orange = Cells(1, nextcol)
Set IRange = Range("A1").Resize(finalrow, nextcol - 4)
IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Orange, Unique:=True
lastrow = Cells(Rows.Count, nextcol).End(xlUp).Row
Cells(1, nextcol).Resize(lastrow, 1).Sort key1:=Cells(1, nextcol), order1:=xlAscending, header:=xlYes
Cells(1, nextcol + 1).Value = "Revenue"
Cells(1, nextcol + 2).Value = "1"
Cells(1, nextcol + 3).Value = "2"
Cells(1, nextcol + 4).Value = "3"
Cells(1, nextcol + 5).Value = "4"
Cells(1, nextcol + 6).Value = "5"
Cells(1, nextcol + 7).Value = "6"
Cells(1, nextcol + 8).Value = "7"
Cells(1, nextcol + 9).Value = "8"
Cells(1, nextcol + 10).Value = "9"
Cells(1, nextcol + 11).Value = "10"
Cells(1, nextcol + 12).Value = "11"
Cells(1, nextcol + 13).Value = "12"
Cells(1, nextcol + 14).Value = "Total"
Cells(2, nextcol + 1).FormulaR1C1 = "=sumif(r2c8:R" & finalrow & "c8,rc[-1],r2c7:r" & finalrow & "C7)"
If lastrow > 2 Then
Cells(2, nextcol + 1).Copy Cells(3, nextcol + 1).Resize(lastrow - 2, 1)
End If
Range("J2:J" & finalrow).FormulaR1C1 = "=VLOOKUP(RC[-1],Date,2,0)"
Range("K2:K" & finalrow).FormulaR1C1 = "=RC[-3]&RC[-1]"
EndRow = Cells(Rows.Count, 13).End(xlUp).Row
Range("O2:Z" & EndRow).FormulaR1C1 = "=SUMIF(R2C11:R" & finalrow & "C11,RC13&R1C,R2C7:R" & finalrow & "C7)"
Range("N2:AA" & EndRow + 1).NumberFormat = "$#,##0_);[Red]($#,##0)"
Range("N" & EndRow + 1 : "Z" & EndRow +1).FormulaR1C1 = "=Sum(R"[EndRow +1]"C:R[-1])"
Range("AA2:AA" & EndRow + 1).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Columns("N:AA").ColumnWidth = 11
Rows("1:1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Columns("AA:AA").Font.Bold = True
End Sub
I want to be able to add a sum for each month at the bottom of the data. This month it may be row 27, next month it may be row 30 or 25.
Here is the line causing me the issue:
Range("N" & EndRow + 1 : "Z" & EndRow +1).FormulaR1C1 = "=Sum(R"[EndRow +1]"C:R[-1])"
I get a Compile Error Expected: List separator
All I want is to add a sum to each known column but to a variable row in columns O thru Z.
Thanks,
Here is the entire macro:
Sub RepsRevenue()
Dim IRange As Range
Dim Orange As Range
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
nextcol = Cells(1, Columns.Count).End(xlToLeft).Column + 4
Range("H1").Copy Destination:=Cells(1, nextcol)
Set Orange = Cells(1, nextcol)
Set IRange = Range("A1").Resize(finalrow, nextcol - 4)
IRange.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Orange, Unique:=True
lastrow = Cells(Rows.Count, nextcol).End(xlUp).Row
Cells(1, nextcol).Resize(lastrow, 1).Sort key1:=Cells(1, nextcol), order1:=xlAscending, header:=xlYes
Cells(1, nextcol + 1).Value = "Revenue"
Cells(1, nextcol + 2).Value = "1"
Cells(1, nextcol + 3).Value = "2"
Cells(1, nextcol + 4).Value = "3"
Cells(1, nextcol + 5).Value = "4"
Cells(1, nextcol + 6).Value = "5"
Cells(1, nextcol + 7).Value = "6"
Cells(1, nextcol + 8).Value = "7"
Cells(1, nextcol + 9).Value = "8"
Cells(1, nextcol + 10).Value = "9"
Cells(1, nextcol + 11).Value = "10"
Cells(1, nextcol + 12).Value = "11"
Cells(1, nextcol + 13).Value = "12"
Cells(1, nextcol + 14).Value = "Total"
Cells(2, nextcol + 1).FormulaR1C1 = "=sumif(r2c8:R" & finalrow & "c8,rc[-1],r2c7:r" & finalrow & "C7)"
If lastrow > 2 Then
Cells(2, nextcol + 1).Copy Cells(3, nextcol + 1).Resize(lastrow - 2, 1)
End If
Range("J2:J" & finalrow).FormulaR1C1 = "=VLOOKUP(RC[-1],Date,2,0)"
Range("K2:K" & finalrow).FormulaR1C1 = "=RC[-3]&RC[-1]"
EndRow = Cells(Rows.Count, 13).End(xlUp).Row
Range("O2:Z" & EndRow).FormulaR1C1 = "=SUMIF(R2C11:R" & finalrow & "C11,RC13&R1C,R2C7:R" & finalrow & "C7)"
Range("N2:AA" & EndRow + 1).NumberFormat = "$#,##0_);[Red]($#,##0)"
Range("N" & EndRow + 1 : "Z" & EndRow +1).FormulaR1C1 = "=Sum(R"[EndRow +1]"C:R[-1])"
Range("AA2:AA" & EndRow + 1).FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
Columns("N:AA").ColumnWidth = 11
Rows("1:1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Columns("AA:AA").Font.Bold = True
End Sub