PDA

View Full Version : Solved: Formula not entered by macro



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

rajkumar
06-21-2009, 08:10 PM
I have a custom function like this in my addin

Function UpTime(StartDate, EndDate, NoOfMcs, DownTime)
Application.Volatile True
tmp = Application.Run("ATPVBAEN.XLA!Networkdays", StartDate, EndDate) * 8.5
UpTime = ((tmp * NoOfMcs) - DownTime) / tmp
UpTime = Application.Round(UpTime, 2)
End Function

mdmackillop
06-22-2009, 12:36 AM
The formula has to reduce to a Text String.
Something (untested) like

.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 & ")"

Bob Phillips
06-22-2009, 12:36 AM
.Cells(LastRow + 10, "B").Formula = _
"=UpTime(D" & LastRow + 5 & ",E" & LastRow + 5 & ",C" & LastRow + 5 & ",O" & LastRow + 2 & ")"

Bob Phillips
06-22-2009, 12:56 AM
Looking at it again, I think your offsets are all wrong



.Cells(LastRow, "B").Formula = _
"=UpTime(D" & LastRow - 6 & ",E" & LastRow - 6 & ",C" & LastRow - 6 & ",O" & LastRow - 10 & ")"

rajkumar
06-23-2009, 08:58 PM
Hi Xld / MD

Thanks for your support. i some how managed to edit the code as required


Here is the final one

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 = "=Max(W2:W" & LastRow & ")" & "-" & "Min(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, Round((Cells(LastRow + 1, "AB").Value), 0) & " / " & Round((Cells(LastRow + 1, "AC").Value), 0), _
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 + 6, "C").Value = "Total Down Time"
Application.ScreenUpdating = True
.Cells(LastRow + 6, "D").Formula = Application.InputBox(prompt:="Please Enter the Down Time in HH.MM format ( and not as HH:MM)", _
Title:="Rajkumar Rao.R")
Application.ScreenUpdating = False
.Cells(LastRow + 11, "B").Formula = _
"=UpTime(D" & LastRow + 5 & ",E" & LastRow + 5 & ",C" & LastRow + 5 & ",D" & LastRow + 6 & ")"
.Cells(LastRow + 5, "C").NumberFormat = 0
.Cells(LastRow + 11, "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



Thanks a Lot :friends:
Raj