Custom Function

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