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
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
I don't see any mega-formulae. Give us an example.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
If you just want to "tidy" your spreadsheet, you can assign a Name to a Formula.
See column L
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Originally Posted by rajkumar
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
Look in Insert/Name/Define.If you just want to "tidy" your spreadsheet, you can assign a Name to a Formula.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Great !Originally Posted by mdmackillop
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
[vba]
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
[/vba]
Kindly help
Thanks
Raj
[vba]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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Fantastic !Originally Posted by mdmackillop
Works well Thanks a lot MD.
Raj