New to Programming

[Guest]

I am new to programming in VBA, and I am having trouble getting my program to
do what I want it too. Basically, We enter names and hours worked into one
sheet, then I want to find that persons hourly rate from another worksheet
and multiply it by their hours. These totals need to be added together to
only include employees of the company and not contractors, material, or
travel (that is what the "M" stands for). I am getting runtime errors when I
run the program. Any help, suggestions or otherwise is greatly appreciated!!

Function Labor()
Dim row As Long
ActiveCell.Formula = 0
For row = 3 To 34
If Worksheets("INPUT").Cells(row, "C").Value = "M" Then
ML =
Application.WorksheetFunction.Index(Worksheets("NAMES").Range("A10:K100"),
Application.WorksheetFunction.Match(Worksheets("INPUT").Cells(row, D).Value,
Worksheets("NAMES").Range("A10:A100"), 1), WorksheetFunction.Match(H1,
Worksheets("NAMES").Range("A10:K10"), 1)) * Cells(row, "H")
ActiveCell.Formula = ActiveCell.Formula + ML
End If
Next row
End Function

Thank you!!

 

[SandyUK]

Hello Anice

I achieve the same thing by using a VLOOKUP formula linking the
workbooks by using the employee number as a primary key in column A.

I find it is a lot easier to manage than a custom VBA function and als
gets around the problem of contractors because they have no employe
number so no calculation is performed.

Regards

Adria

 

[SandyUK]

Hello Anice

I achieve the same thing by using a VLOOKUP formula linking the
workbooks by using the employee number as a primary key in column A.

I find it is a lot easier to manage than a custom VBA function and als
gets around the problem of contractors because they have no employe
number so no calculation is performed.

Regards

Adria

 

[Andrew Taylor]

I suspect that instead of

Application.WorksheetFunction.Match(Worksheets("INPUT").Cells(row, D).Value, you should have
Application.WorksheetFunction.Match(Worksheets("INPUT").Cells(row, "D").Value,

The first version tries to variable called D, and as it's unset, it's
an invalid
parameter to the Cells method.

As a general point, I'd strongly recommend that you use Option Explicit
for all your macros (set on "Require Variable Declaration" in VBA
Tools/Options). This would have caught the error by telling you that
D was undefined.

I'm also unsure what the line

ActiveCell.Formula = ActiveCell.Formula + ML

is supposed to do: maybe you mean

ActiveCell.Value = ActiveCell.Value + ML

hth
Andrew Taylor

 

[Guest]

I made those changes, that was actually a typo that I left off the quotations
around the q. I'm still getting an error that says "Unable to get the Match
Property of the WorksheetFunction class" Any more tips??

Thank you again for everything!

 

[Andrew Taylor]

The H1 in

...WorksheetFunction.Match(H1,Worksheets("NAMES").Range("A10:K10"), 1))...

looks wrong too, if you mean cell H1 (in which worksheet?). You would
need
to replace it by Worksheets("whatever").Range("H1").

For debugging stuff like this, it can help to split up the complicated
expressions, e.g.:
(BTW, for brevity you can omit "WorksheetFunction)

dim m1 as long, m2 as long
m1 = Application.Match(Worksheets("INPUT").Cells(row, D).Value, _
Worksheets("NAMES").Range("A10:A100"), 1)
m2 = Application.Match(H1,Worksheets("NAMES").Range("A10:K10"), 1)
ML = _
Application.Index(Worksheets("NAMES").Range("A10:K100"),m1, m2) _
* Cells(row, "H")

It's then clearer which Match is causing the problem (I suspect the
second one, because of the "H1")

You could also make things clearer by using variables for the
Worksheets:

Dim wsNames as Worksheet, wsInput as Worksheet

set wsNames = Workheets("NAMES")
set wsInput = Workheets("Input")
'' and then
m1 = Application.Match(wsInput.Cells(row, D).Value,
wsNames.Range("A10:A100"), 1)
'' etc...


(BTW I'm sure this could all be done with just worksheet functions. It
feels as if you're overcomplicating things a lot.)

Andrew