PDA

View Full Version : Solved: Function Required for Mega Formula



rajkumar
08-19-2008, 11:08 PM
Hi,
I'm a newbie for VBA, anyone can help me? with a UDF for my mega formaulas in the attachment. I need to do this report every month.

Thanks in advance
raj

Bob Phillips
08-20-2008, 12:37 AM
I don't see any mega-formulae. Give us an example.

mdmackillop
08-20-2008, 05:36 AM
If you just want to "tidy" your spreadsheet, you can assign a Name to a Formula.
See column L

rajkumar
08-21-2008, 08:03 AM
Hi Xld,
Columns L, M, N contains mega formulae in the attachment.

At column L i am comparing the date difference of customer service complaint registered to engineer arrived at customer primises.

Similarly the column heading is self explantory. This is for printing machines log report.

Any help on UDF is very much appreciated.

Thanks
Raj

Bob Phillips
08-21-2008, 08:08 AM
Sorry, I just don't see them as mega-formulae, and cannot see what you hope to achieve with a UDF, it will almost inevitably be slower.

rajkumar
08-21-2008, 08:12 AM
Hi Xld,
Columns L, M, N contains mega formulae in the attachment.

At column L i am comparing the date difference of customer service complaint registered to engineer arrived at customer primises.

Similarly the column heading is self explantory. This is for printing machines log report.

Any help on UDF is very much appreciated.

Thanks
Raj


Hi Md,

I could see the formulae = BMC in L column, but the function is not there in sheet object or thisworkbook object.

Thanks
Raj

mdmackillop
08-21-2008, 08:21 AM
If you just want to "tidy" your spreadsheet, you can assign a Name to a Formula.

Look in Insert/Name/Define.

rajkumar
08-22-2008, 12:12 PM
Look in Insert/Name/Define.

Great !

I use a macro which fills this formula in the cells, how can i modify to do it in vba as you did.

The macro i use is

Sub BATCHMODE(ByVal fName As String)
Columns("N:N").Select
Selection.Copy
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Range("L1").FormulaR1C1 = "Batch Mode Calls"

Dim myRng As Range

Dim lastRw As Long
sSheetName = Left(fName, Len(fName) - 4)
lastRw = Worksheets(sSheetName).Range("L2").End(xlDown).Row

With Worksheets(sSheetName).Range("L2")

.Formula = "=IF(ISERROR(DATEDIF(RC[4],RC[6],""D"")),IF(NOT(ISBLANK(RC[6]))," & _
"(DATEDIF(RC[6],RC[4],""D"")&"" DAYS BATCH MODE CALL""),""BLANK DATA"")," & _
"DATEDIF(RC[4],RC[6],""D""))"

.AutoFill Destination:=Worksheets(sSheetName).Range("L2:L" & lastRw&)

End With
Columns("L:L").Select
Selection.NumberFormat = "0"

End Sub

Kindly help

Thanks
Raj

mdmackillop
08-22-2008, 02:53 PM
Sub Macro1()
Dim Rw As Long
Rw = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="BMC", RefersToR1C1:= _
"=IF(ISERROR(DATEDIF('LOG-BLR'!RC[5],'LOG-BLR'!RC[7],""D"")),IF(NOT(ISBLANK('LOG-BLR'!RC[7]))," & _
"(DATEDIF('LOG-BLR'!RC[7],'LOG-BLR'!RC[5],""D"")&"" DAYS BATCH MODE CALL""),""BLANK DATA"")," & _
"DATEDIF('LOG-BLR'!RC[5],'LOG-BLR'!RC[7],""D""))"
Range("L2:L" & Rw).FormulaR1C1 = "=BMC"
End Sub

rajkumar
08-23-2008, 02:10 AM
Sub Macro1()
Dim Rw As Long
Rw = Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Names.Add Name:="BMC", RefersToR1C1:= _
"=IF(ISERROR(DATEDIF('LOG-BLR'!RC[5],'LOG-BLR'!RC[7],""D"")),IF(NOT(ISBLANK('LOG-BLR'!RC[7]))," & _
"(DATEDIF('LOG-BLR'!RC[7],'LOG-BLR'!RC[5],""D"")&"" DAYS BATCH MODE CALL""),""BLANK DATA"")," & _
"DATEDIF('LOG-BLR'!RC[5],'LOG-BLR'!RC[7],""D""))"
Range("L2:L" & Rw).FormulaR1C1 = "=BMC"
End Sub


Fantastic !

Works well Thanks a lot MD.
Raj