Hi, I've written the following function in VBA that returns the expected output when run through a sub, but returns a #VALUE error when used in the worksheet. The function essentially wants to look up which sales mechanics (from the called workbook) have been utilized in periods where the spend is greater than zero.
I have other functions working in a similar way that don't return errors. Does anyone have any suggestions?
I have other functions working in a similar way that don't return errors. Does anyone have any suggestions?
VBA Code:
Function Mechanics(myRange As Range)
Dim i, j As Integer
Dim str As String
Dim wb As Workbook
Dim checkArray() As Variant
Dim outputArray() As String
Application.ScreenUpdating = False
'If no mechanics, leave blank
If WorksheetFunction.CountIfs(myRange, ">0") = 0 Then
Mechanics = ""
Exit Function
Else
str = ""
'Loop through range, checking if there has been spend
For i = 1 To myRange.Cells.Count
'If there has, add mechanic name to string
If myRange.Offset(i - 1, 0).Resize(1, 1).Value > 0 Then
str = str & myRange.Offset(i - 1, 6 - myRange.Column).Resize(1, 1).Value
End If
Next i
'Open Mechanic lookup workbook and copy contents to an array
Set wb = Workbooks.Open("[I]Path to mechanic lookup file[/I]")
wb.Activate
checkArray = Range("A2:B" & WorksheetFunction.CountA(Range("A:A"))).Value
wb.Close (False)
j = 0
ReDim outputArray(j)
'Loop through the array lookup column
For i = 1 To UBound(checkArray, 1)
'If lookup item is in string, add mechanic to output array
If InStr(1, str, checkArray(i, 1), 1) > 0 Then
outputArray(j) = checkArray(i, 2)
j = j + 1
ReDim Preserve outputArray(j)
End If
Next i
'Return a comma delimited list of mechanics
For i = LBound(outputArray) To UBound(outputArray) - LBound(outputArray) - 1
Mechanics = Mechanics & outputArray(i) & ", "
Next i
Mechanics = Left(Mechanics, Len(Mechanics) - 2)
End If
Application.ScreenUpdating = True
End Function