The data is in columns A through G and the following macro tests cells in column A to determine when there is a change in the month, if this is found, two rows are inserted, along with a text cell in column G - "Monthly Total" This code seems to work fine for that part:
Sub newinsert()
'to sub total territory and total months'
Application.ScreenUpdating = True
Dim count As Integer
Dim X As Integer
Range("a4").Select
For count = 1 To 500
If ActiveCell.Value = "" Then End
If ActiveCell.Value <> ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.Insert
ActiveCell.Offset(0, 6).Select
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlRight
ActiveCell.FormulaR1C1 = "=""Monthly Total"""
ActiveCell.Offset(2, -6).Select
For X = 1 To 1
Next X
Else
ActiveCell.Offset(1, 0).Range("a1").Select
End If
Next count
End Sub
However, I now want to add the monthly amounts in column G (the range will vary from month-to-month) and place the result in column H, next to the "Monthly Total" text.
Once this is complete, I would also like to sum amounts based on a change in location (as determined in column D) and place these amounts in column H. If it is easier to total the location piece first, then the monthly amount, that's also fine. When I enter the following line of code:
Application.WorksheetFunction.Sum (Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset.End(xlUp)))
directly before the Selection.Font.Bold = True
I get the following error:
Run Time Error 1004
Sum method of WorksheetFunction class failed
I probably have too many Ifs, and Counts combined in the same code, but my basic knowledge certainly appears to be a "dangerous thing" in this case. I would love to solve this, and learn as I proceed, but at this stage I think I am in over my head and cannot figure out what is wrong with the overall code.
I cannot post the worksheet because I don't have admin. rights to download the right tools.
Thanks in advance for any help or direction you can provide.
Try tweaking it somewhat, keep track of the last insert (w/ MyRw). Here's an example:
Sub NewInsrtDeux()
Dim cl As Range, myRw As Long
myRw = 4
For Each cl In Range(Cells(4, 1), Cells(65536, 1).End(3))
If cl(2) <> cl And Not IsEmpty(cl) And Not IsEmpty(cl(2)) Then
cl(2).EntireRow.Insert
With cl(2, 7)
.Font.Bold = True
.HorizontalAlignment = xlRight
.Value = "Monthly Total"
End With
cl(2, 8) = Application.Sum(Range(Cells(myRw, 7), Cells(cl.Row, 7)))
myRw = cl(2).Row: Set cl = cl(2)
End If
Next
Set cl = Cells(65536, 1).End(3)(2, 7)
With cl
.Font.Bold = True
.HorizontalAlignment = xlRight
.Value = "Monthly Total"
End With
cl(, 2) = Application.Sum(Range(Cells(myRw, 7), Cells(cl(0).Row, 7)))
End Sub
Hope this helps.
Edit: Paste this into Excel before taking it to your module, working on the fix for this. :)
Thanks so much for the help - and the speed with which you posted it.
I have tested the code you sent and it works just fine. In my ongoing need to learn more about this magical VB stuff, I am going to try and analyze your code and make a few modifications to see if I can get a few different things to work.
Once again, thanks.
Neville.
You're welcome!
I'll be the first to admit, the code I posted is a little sloppy. A more appropriate way to approach this might be to loop backwards through a long integer... I went to a great extent to work with a range variable and I'm relatively certain there's an extra, unecessary cell check in there.
However, it's functional and fairly quick.
Yes, vb is fun and powerful. Best in your pursuit. :)
#If you have any other info about this subject , Please add it free.# |