Custom Function [Solved/Closed]

Report
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012
-
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Hello,

wud appreciate help on me building a custom function...i've written a function which shud do the following
function in sheet A will get empid and date from sheet A, lookup the empid and date and get salary from sheet B, then based on salary amount , will lookup sheet C and get percentage, then salary / 12 * percentage is calculated and shown as value in sheet A.

m unable to select sheet B to get the salary.

----------------------------------------------------------------------------------------------------------
Public salary As Long
Function projections(emp_id As Long) As Long
Dim ThisRow As Long
Dim cutoff As Date
Application.Volatile
'Dim emp_id As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'declare sheet names
contr_indiv = "Contributions - Individual"
contr_mem = "Contributions - Member"
contr_sheet = ActiveSheet.Name


If contr_sheet = contr_indiv Then
ThisRow = Sheets("Settings").Cells(1, 3)
current_row = ThisRow + 1
myrow = ActiveCell.Row
mycol = ActiveCell.Column
With ActiveSheet
empid_col = getcol(ThisRow, "Employee ID")
emp_id = .Cells(myrow, getcol(ThisRow, "Employee ID"))
cutoff = ActiveSheet.Cells(ThisRow, mycol)
End With

Call get_salary
Call get_percent


'calculate and update values
Sheets(contr_indiv).Activate
indiv_contr = (salary / 12) * ind_percent
projections = indiv_contr

ElseIf contr_sheet = contr_mem Then
ThisRow = Sheets("Settings").Cells(2, 3)
current_row = ThisRow + 1



Else
MsgBox "Projections Functions not programmed to run on " & ActiveSheet.Name & " Sheet."
ActiveCell.Clear
End If

Application.Calculation = xlCalculationAutomatic
End Function
-----------------------------------------------------------------------------------------------------------------
Function getcol(ThisRow As Long, ColHeading As String, Optional Loc As Object) As Integer
'Finds the column headed ColHeading in ThisRow in sheet:SheetName
Dim CHLen As Integer
Dim active_book_name As String
getcol = 1
CHLen = Len(ColHeading)
If Loc Is Nothing Then Set Loc = Sheets(ActiveSheet.Name)
With Loc
Do Until LCase(Trim(.Cells(ThisRow, getcol))) = LCase(Trim(ColHeading)) Or getcol = 256
getcol = getcol + 1
Loop
End With
If getcol = 256 Then MsgBox "Can't find '" & ColHeading & "' in Row " & ThisRow: End

If getcol = 256 Then
active_book_name = ActiveWorkbook.Name

MsgBox "Please check Field Name in " & active_book_name & " and try again"
If Windows(active_book_name).Visible = True Then
Windows(active_book_name).Close (False)
Else
End If
End
Else

End If

End Function
----------------------------------------------------------------------------------------------------------
Sub get_salary()
Application.ScreenUpdating = False
Dim ThisRow As Long
'get salary data from Employee Data
Sheets("Employee Data").Select
ThisRow = Sheets("Settings").Cells(3, 3)
With Sheets("Employee Data")
empid_col = getcol(ThisRow, "Employee ID")
cutoff_col = ActiveSheet.Cells(ThisRow, cutoff)
'emp_id = ActiveSheet.Cells(myrow, getcol(ThisRow, empid_col))
End With

With Sheets("Employee Data").Columns(empid_col)
Set c = .Find(emp_id, lookat:=xlWhole)
If Not c Is Nothing Then
salary = c.Offset(0, cutoff_col)
Else
End If
End With

End Sub
----------------------------------------------------------------------------------------------------------------

Sub get_percent()
Application.ScreenUpdating = False
'get % from options sheet
Sheets("Options").Activate
col = 3
Row = 6

If activehseet.Cells(Row, col) <= salary Then
ind_percent = ActiveSheet.Cells(Row + 1, col)
Else
End If
col = col + 1

Do While ActiveSheet.Cells(Row, col) <> ""
If activehseet.Cells(Row, col - 1) > salary And ActiveSheet.Cells(Row, col) <= salary Then
ind_percent = ActiveSheet.Cells(Row + 1, col)
Else
ind_percent = ActiveSheet.Cells(Row + 1, col)
End If
col = col + 1
Loop


End Sub

2 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
Could you post a sample book to test the function
smd_excel
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

smd_excel
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

i have written the function to correct the problem i earlier requested. theres another problem now.....if i drag the formula across the column the values are not getting updated. i need press F2 in every cell of the formula to get the new values. and when i save the workbook all the formulas get recalculated with the same value which equals to the value in the last cell.
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
Could you re-post your updated workbook. I am not sure what exactly you are asking now. How ever in general, see if the comments below helps

one way could be that you can mark the function as volatile
Application.Volatile

Other thing that you mention is that if you drag across the column. In the book that you have posed you had a call like
=projections(A5) in cell e5
now if you drag this to f5, it will become =projections(b5) in

I think you need to achor it to column A, so dragging does not impact the formula as
=projections($A5)
smd_excel
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

pls find uploaded file link http://www.speedyshare.com/files/23369282/EBT_-_Projections.xlsm
let me explain the entire scenario again.
in the Contributions - Individual sheet, col D5 shud have a custom formula which will take the emp_id in A5 (10050001), cutoff (31-Aug-10) from D4 and search for the emp_id is sheet Employee Data column 1 and find the cutoff in row 7 (if cutoff is greater than G7 and less H7) then v shud get the salary i.e. 156000. next go to the options sheet , row 6, check where 156000 falls in this case it will be in D6, take the % in the below cell which is 5%. then in the Contributions - Individual sheet in cell D5 (or any other cell in which the custom function is in) it shud give the value of ((salary / 12) * percentage) .
in the file i uploaded i've managed to do the above i just explained. now when i drag the custom formula (chk sheet Contributions - Individual) in cell E6 till the end, the same value in cell E6 is shown in all cells. unless i hit the F2 button and enter does the value change. if u go to cell E115 which i've highlighted in yellow, the function doenst get calculated. i need to press F2 in E115 and then enter for the values to b calculated.
smd_excel
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

hi ....any updates to the above request....
Posts
27
Registration date
Saturday April 18, 2009
Status
Member
Last seen
November 22, 2012

the File is uploaded - http://www.speedyshare.com/files/23359445/EBT_-_Projections.xlsm
i have written the function to correct the problem i earlier requested. theres another problem now.....if i drag the formula across the column the values are not getting updated. i need press F2 in every cell of the formula to get the new values. and when i save the workbook all the formulas get recalculated with the same value which equals to the value in the last cell.