VBA UDF Calculation Error

[markmcmillan]

Hello,

I am developing a function that is incorporated into a project management spreadsheet. Each month we must produce a breakdown of how many hours we have spent on a project activity, the value of that work for the month and running cumulative total. it would be easy to use a "vlookup" however the difficulty is that each month new activities of people can be added resulting in a different vlookup range and column index number is required. I have written a UDF that can define these and it works. the code is shown below.

However, when i open a another workbook the UDF return #VALUE.

I understand that this is a result of the UDF looking to the new workbook to define sheets and ranges and have tried to compensate for this. After some trial and error testing I have found that the error only occurs when I use the vlookupformula at the end. If I set the UDF to return one of the intermittent variants the error does occur.

Does anybody have any idea what I'm doing wrong.

I appreciate any advice

Thank you

Function TotalExpend(cal As Variant, Month As Range, itno As Variant)

'Calculate Total Spent so Far
'Determine Name of Previous Tab
Dim wb As Workbook
Set wb = ThisWorkbook
Dim Csheet
Dim c
c = Application.WorksheetFunction.VLookup(Application.WorksheetFunction.VLookup(cal, wb.Sheets("Months").Range("C3:D50"), 2, False) - 1, wb.Sheets("Months").Range("B3:C50"), 2, False)
'Determine where array is in previous tab
Csheet = "Actual Costs (" & c & ")"
Dim d As Range
Dim e
Dim f
f = Month.Rows.Count
Set d = wb.Sheets(Csheet).Range("B13:BZ17")
For Each rr In d
If rr.Value = "Total Actual Expenditure" Then
e = Sheets(Csheet).Range(rr.Address).Offset(f, 0).Address
Exit For
End If
Next

Dim g
g = wb.Sheets(Csheet).Range(Month(1, 1).Address).Address & ":" & wb.Sheets(Csheet).Range(e).Address

Dim h
h = wb.Sheets(Csheet).Range(g).Columns.Count
TotalExpend = Application.WorksheetFunction.VLookup(itno, wb.Sheets(Csheet).Range(g), h, False)

End Function

 

[pgc01]

After some trial and error testing I have found that the error only occurs when I use the vlookup formula at the end. If I set the UDF to return one of the intermittent variants the error does occur.

Hi Mark
Welcome to the board

Well, you should look into that formula.

What is itno? There's nothing in your post that explains what it is.

Just before the vlookup formula is executed:

what's the value of itno?
What is the value of g?
What is the value of h?

Did you confirm that itno exists in the first columns of range(g)?

Please answer all these questions.

Remark: it's good that you have declared the variables, but the declarations are poor. It would be better if you would specify the types. That will help not only you but also anyone else that reads the code. Also the names of the variables could be more informative.