Solved: Summary using VBA

[rajkumar]

Hi Experts,

I need a help in summarizing my report, i have macro that imports a text file and saves as xls.

this report has history of service calls done for a printer

what i want to add in the tail of my macro is to summarize the number of calls, response time etc after the last used row under respective columns.

i have attached a sample workbook here. Yellow highlighted ones are manually done. i want this to be automated.

Please help

Raj

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Cells(LastRow + 1, "D").Value = "Late Evening Visit"
.Cells(LastRow + 1, "E").Formula = "=COUNTIF(E2:E" & LastRow & ",""late evening call"")"
.Cells(LastRow + 1, "J").Resize(3).Value = Application.Transpose(Array("Break Down", "PM/SM Call", "ROT"))
.Cells(LastRow + 1, "K").Resize(3).Formula = "=COUNTIF($K$2:$K$" & LastRow & ",J" & LastRow + 1 & ")"
.Cells(LastRow + 1, "N").Formula = "=AVERAGE(N2:N" & LastRow & ")"
.Cells(LastRow + 2, "N").Value = "AVG RT"
.Cells(LastRow + 1, "O").Formula = "=SUM(O2:O" & LastRow & ")"
.Cells(LastRow + 2, "O").Value = "TOTAL DT"
.Cells(2, "W").Resize(LastRow - 1).Formula = "=V2+U2"
.Cells(LastRow + 1, "V").Value = "Prints Taken"
.Cells(LastRow + 1, "W").Formula = "=W2-W" & LastRow
.Cells(LastRow + 1, "AB").Resize(, 2).Formula = "=AVERAGE(AB2:AB" & LastRow & ")"
End With

End Sub

[rajkumar]

Hi Xld,
I'm Sorry , i was out of town.

Great! it works ! as needed.

Thank you so much.

Raj :friends:

[rajkumar]

Hi Xld,
One More help on the same thread. some of my report has meter reading of Cell W2 is zero in that case i want to subtract lastcell of that column from next cell which is not zero.

i tried myself to modify the macro posted by you but i could not succeed
can you please help me on that

Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet
sSheetName = Left(fName, Len(fName) - 4)
With Worksheets(sSheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Cells(LastRow + 1, "D").Value = "Late Evening Visit"
.Cells(LastRow + 1, "E").Formula = "=COUNTIF(E2:E" & LastRow & ",""late evening call"")"
.Cells(LastRow + 1, "J").Resize(3).Value = Application.Transpose(Array("Break Down", "PM/SM Call", "ROT"))
.Cells(LastRow + 1, "K").Resize(3).Formula = "=COUNTIF($K$2:$K$" & LastRow & ",J" & LastRow + 1 & ")"
.Cells(LastRow + 1, "N").Formula = "=AVERAGE(N2:N" & LastRow & ")"
.Cells(LastRow + 2, "N").Value = "AVG RT"
.Cells(LastRow + 1, "O").Formula = "=SUM(O2:O" & LastRow & ")"
.Cells(LastRow + 2, "O").Value = "TOTAL DT"
.Cells(2, "W").Resize(LastRow - 1).Formula = "=V2+U2"
.Cells(LastRow + 1, "V").Value = "Prints Taken"
.Cells(LastRow + 1, "W").Formula = "=W2-W" & LastRow & ""
.Cells(LastRow + 1, "W").Formula = "=IF(W2=0,"OFFSET(W2,1,0)-W" & LastRow & ",W2-W" & LastRow & ")"
.Cells(LastRow + 1, "AB").Resize(, 2).Formula = "=AVERAGE(AB2:AB" & LastRow & ")"
End With


Thanks In Advance
Raj:think: