Solved: Function Required for Mega Formula

[rajkumar]

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]

I don't see any mega-formulae. Give us an example.

[mdmackillop]

If you just want to "tidy" your spreadsheet, you can assign a Name to a Formula.
See column L

[rajkumar]

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]

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]

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]

If you just want to "tidy" your spreadsheet, you can assign a Name to a Formula.

Look in Insert/Name/Define.

[rajkumar]

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]

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]

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