Excel Custom Function
Solved/Closed
Related:
- Excel Custom Function
- Ultimate custom night - Download - Horror
- Custom resolution utility - Download - Customization
- Dino game custom character - Guide
- Excel marksheet - Guide
- Number to words in excel - Guide
6 responses
Maybe you tried this before, but this may be the cause:
Go to Tools
Options
Calculation
Make sure it is in AUTOMATIC and not MANUAL
Go to Tools
Options
Calculation
Make sure it is in AUTOMATIC and not MANUAL
Hi,
I had the same problem once with my MS excel worksheet before.. When I input a formula in a cell and copied it to other cells, it copies the formula but displays the one from the originating cell. I had to press F2 then hit enter to "refresh" the cell. It would now then display the correct figure for the copied formula.
I didn't get to fix this problem with the said worksheet though. I just opened a new worksheet and all worked fine. I guess there was just some sort of bug with the file itself. Hope this helps.
I had the same problem once with my MS excel worksheet before.. When I input a formula in a cell and copied it to other cells, it copies the formula but displays the one from the originating cell. I had to press F2 then hit enter to "refresh" the cell. It would now then display the correct figure for the copied formula.
I didn't get to fix this problem with the said worksheet though. I just opened a new worksheet and all worked fine. I guess there was just some sort of bug with the file itself. Hope this helps.
Use text to Column option in excel
kallor234
Posts
18
Registration date
Thursday May 15, 2008
Status
Member
Last seen
May 29, 2009
3
Sep 15, 2008 at 05:34 AM
Sep 15, 2008 at 05:34 AM
hello moses,
well i think this isnt unforfunately possible to be done in excel if you were using infopath then it could have been possible
well i think this isnt unforfunately possible to be done in excel if you were using infopath then it could have been possible
hi...thanks for the try...maybe if u have a look at the code u can get a better picture
all is want is the formula to refresh itslef when u open or close workbook.
Function dis_multiple(end_date As Variant, msr_type As String, scheme As String, category As String, dob As Variant) As Variant
Dim title_row As Long
Dim age As Long
'Dim end_date As Date
Application.Calculation = xlCalculationAutomatic
Row = ActiveCell.Row
title_row = 5
age = Year(Now()) - Year(dob)
With ActiveSheet
msr_type = .Cells(Row, GetCol(title_row, "MSR Type"))
scheme = .Cells(Row, GetCol(title_row, "Scheme"))
category = .Cells(Row, GetCol(title_row, "Leaver Category"))
dob = .Cells(Row, GetCol(title_row, "DOB"))
end_date = .Cells(Row, GetCol(title_row, "Last Day Of Paid Service / Employment End Date"))
End With
end_month = Month(end_date)
'If msr_type = "" Or scheme = "" Or dob > 1900 Then
If msr_type = "New Joiners" And dob > 1900 Then
If scheme = "SORAS/DIS" Then
If age < 58 Then
dis_multiple = 4
Else
dis_multiple = 1
End If
Else
End If
Else
'MsgBox "Please see that the MSR Type and Date of Birth fields are filled in for New Joiners"
'Exit Function
End If
If msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "SORAS/DIS" Then
dis_multiple = 4
Else
End If
Else
End If
If msr_type = "Leavers" And scheme = "SORAS/DIS" Then
If category = "Regular" Then '
If end_month = Month(Now()) Then
dis_multiple = 4
Else
dis_multiple = 1
End If
ElseIf category = "Absconder" Then
dis_multiple = 4
ElseIf category = "Voluntary" Then
dis_multiple = 1
Else
MsgBox "Leaver category is blank"
End If
Else
End If
If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If
Else
End If
If msr_type = "Leavers" And scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If
If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "MS" Then
dis_multiple = 1
Else
End If
Else
End If
If msr_type = "" And scheme = "" And category = "" And dob = "" And end_date = "" Then
dis_multiple = ""
Else
End If
If scheme = "MS" Then
dis_multiple = ""
Else
End If
Application.Calculation = xlCalculationAutomati
End Function
all is want is the formula to refresh itslef when u open or close workbook.
Function dis_multiple(end_date As Variant, msr_type As String, scheme As String, category As String, dob As Variant) As Variant
Dim title_row As Long
Dim age As Long
'Dim end_date As Date
Application.Calculation = xlCalculationAutomatic
Row = ActiveCell.Row
title_row = 5
age = Year(Now()) - Year(dob)
With ActiveSheet
msr_type = .Cells(Row, GetCol(title_row, "MSR Type"))
scheme = .Cells(Row, GetCol(title_row, "Scheme"))
category = .Cells(Row, GetCol(title_row, "Leaver Category"))
dob = .Cells(Row, GetCol(title_row, "DOB"))
end_date = .Cells(Row, GetCol(title_row, "Last Day Of Paid Service / Employment End Date"))
End With
end_month = Month(end_date)
'If msr_type = "" Or scheme = "" Or dob > 1900 Then
If msr_type = "New Joiners" And dob > 1900 Then
If scheme = "SORAS/DIS" Then
If age < 58 Then
dis_multiple = 4
Else
dis_multiple = 1
End If
Else
End If
Else
'MsgBox "Please see that the MSR Type and Date of Birth fields are filled in for New Joiners"
'Exit Function
End If
If msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "SORAS/DIS" Then
dis_multiple = 4
Else
End If
Else
End If
If msr_type = "Leavers" And scheme = "SORAS/DIS" Then
If category = "Regular" Then '
If end_month = Month(Now()) Then
dis_multiple = 4
Else
dis_multiple = 1
End If
ElseIf category = "Absconder" Then
dis_multiple = 4
ElseIf category = "Voluntary" Then
dis_multiple = 1
Else
MsgBox "Leaver category is blank"
End If
Else
End If
If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If
Else
End If
If msr_type = "Leavers" And scheme = "DIS/MS" Then
dis_multiple = 1
Else
End If
If msr_type = "New Joiners" Or msr_type = "Salary Alterations" Or msr_type = "Multiple Change" Or msr_type = "Transfers" Then
If scheme = "MS" Then
dis_multiple = 1
Else
End If
Else
End If
If msr_type = "" And scheme = "" And category = "" And dob = "" And end_date = "" Then
dis_multiple = ""
Else
End If
If scheme = "MS" Then
dis_multiple = ""
Else
End If
Application.Calculation = xlCalculationAutomati
End Function
Didn't find the answer you are looking for?
Ask a question
Hello,
Have you tried the following:
Private Sub Workbook_Open()
Application.CalculateBeforeSave = True
End Sub
'the following can be resources consuming if you have a lot of formulas to calculate on the sheet
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Calculate
End Sub
Have you tried the following:
Private Sub Workbook_Open()
Application.CalculateBeforeSave = True
End Sub
'the following can be resources consuming if you have a lot of formulas to calculate on the sheet
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Calculate
End Sub
