rajkumar
06-21-2009, 08:07 PM
Hi,
I have a macro that imports a text files and deletes unwanted characters then does certain calculations. Here is my macro
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, "AB").Resize(, 2).Formula = "=AVERAGE(AB2:AB" & LastRow & ")"
.Cells(LastRow + 1, "AB").Resize(, 2).NumberFormat = 0
.Cells(LastRow + 4, "A").Resize(, 2).Value = Array("Parameters", "Value")
.Cells(LastRow + 5, "A").Resize(7).Value = Application.Transpose(Array("Unscheduled Maintenance Calls", _
"Scheduled Maintenance Calls", " Average Response Time", " Total Prints / Copies Done", _
" Average PBC /DBC", " No.Of Customer Care Calls", "Total UpTime"))
.Cells(LastRow + 5, "B").Resize(6).Value = Application.Transpose(Array(Cells(LastRow + 1, "K").Value, Cells(LastRow + 2, "K").Value, _
Cells(LastRow + 1, "N").Value, Cells(LastRow + 1, "W").Value, (Cells(LastRow + 1, "AB").Value) & " / " & (Cells(LastRow + 1, "AC").Value), _
Cells(LastRow + 3, "K").Value))
.Cells(LastRow + 7, "B").NumberFormat = "hh:mm;@"
.Cells(LastRow + 4, "C").Resize(, 3).Value = Array("No Of Machines", "Start Date", "End Date")
.Cells(LastRow + 5, "C").Formula = Application.InputBox(prompt:="Please Enter the No OF Machines", _
Title:="Rajkumar Rao.R")
.Cells(LastRow + 5, "D").Formula = Application.InputBox(prompt:="Please Enter the Start Date in DD-MMM-YY format", _
Title:="Rajkumar Rao.R")
.Cells(LastRow + 5, "E").Formula = Application.InputBox(prompt:="Please Enter the End Date in DD-MMM-YY format", _
Title:="Rajkumar Rao.R")
.Cells(LastRow + 10, "B").Formula = UpTime(Cells(LastRow + 5, "D").Value, _
Cells(LastRow + 5, "E").Value, Cells(LastRow + 5, "C").Value, Cells(LastRow + 2, "O").Value))
.Cells(LastRow + 5, "C").NumberFormat = 0
.Cells(LastRow + 10, "B").NumberFormat = "0%"
.Cells(LastRow + 4, "A").CurrentRegion.Select
Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Italic = False
End With
Cells.EntireColumn.AutoFit
In this the row i have highlighted in Bold Blue is not working, ie: there is no formula being entered by this macro to that cell.
Can any one help me to understand what is wrong with this?
I have attached a sample output
Thanks in Advance
I have a macro that imports a text files and deletes unwanted characters then does certain calculations. Here is my macro
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, "AB").Resize(, 2).Formula = "=AVERAGE(AB2:AB" & LastRow & ")"
.Cells(LastRow + 1, "AB").Resize(, 2).NumberFormat = 0
.Cells(LastRow + 4, "A").Resize(, 2).Value = Array("Parameters", "Value")
.Cells(LastRow + 5, "A").Resize(7).Value = Application.Transpose(Array("Unscheduled Maintenance Calls", _
"Scheduled Maintenance Calls", " Average Response Time", " Total Prints / Copies Done", _
" Average PBC /DBC", " No.Of Customer Care Calls", "Total UpTime"))
.Cells(LastRow + 5, "B").Resize(6).Value = Application.Transpose(Array(Cells(LastRow + 1, "K").Value, Cells(LastRow + 2, "K").Value, _
Cells(LastRow + 1, "N").Value, Cells(LastRow + 1, "W").Value, (Cells(LastRow + 1, "AB").Value) & " / " & (Cells(LastRow + 1, "AC").Value), _
Cells(LastRow + 3, "K").Value))
.Cells(LastRow + 7, "B").NumberFormat = "hh:mm;@"
.Cells(LastRow + 4, "C").Resize(, 3).Value = Array("No Of Machines", "Start Date", "End Date")
.Cells(LastRow + 5, "C").Formula = Application.InputBox(prompt:="Please Enter the No OF Machines", _
Title:="Rajkumar Rao.R")
.Cells(LastRow + 5, "D").Formula = Application.InputBox(prompt:="Please Enter the Start Date in DD-MMM-YY format", _
Title:="Rajkumar Rao.R")
.Cells(LastRow + 5, "E").Formula = Application.InputBox(prompt:="Please Enter the End Date in DD-MMM-YY format", _
Title:="Rajkumar Rao.R")
.Cells(LastRow + 10, "B").Formula = UpTime(Cells(LastRow + 5, "D").Value, _
Cells(LastRow + 5, "E").Value, Cells(LastRow + 5, "C").Value, Cells(LastRow + 2, "O").Value))
.Cells(LastRow + 5, "C").NumberFormat = 0
.Cells(LastRow + 10, "B").NumberFormat = "0%"
.Cells(LastRow + 4, "A").CurrentRegion.Select
Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Italic = False
End With
Cells.EntireColumn.AutoFit
In this the row i have highlighted in Bold Blue is not working, ie: there is no formula being entered by this macro to that cell.
Can any one help me to understand what is wrong with this?
I have attached a sample output
Thanks in Advance