3

I have an excel VBA Function that needs to count the cells that are coloured, match a specific string and have another cell blank.

I created it in a test workbook using only a small sample of the data where it worked, but now, when I implement in the main workbook, it returns #Value! I suspect there could be an issue with the string being used, but putting a watch on all my variables shows the function is working as expected but it doesn't actually provide the return value.

Here is the code. Any input will be very much appreciated

Function FundInStarted(rSample As Range, rKRM As Range, rColNum As Range, rArea As Range) As Long

    Dim rAreaCell As Range
    Dim lMatchColor As Long
    Dim lCounter As Long
    Dim sKRMMatch As String
    Dim lColNumOff As Long

    lMatchColor = rSample.Interior.Color
    sKRMMatch = rKRM.Value2
    lColNumOff = rColNum

    For Each rAreaCell In rArea
        If rAreaCell.Interior.Color = lMatchColor And rAreaCell.Offset(0, -lColNumOff) = sKRMMatch And rAreaCell.Value = "" Then
            lCounter = lCounter + 1
        End If
    Next
    FundInStarted = lCounter
End Function
Vityata
  • 42,633
  • 8
  • 55
  • 100
MistyBlue
  • 31
  • 3
  • 1
    Have you debugged the value of `lCounter`? Insert a line with `debug.print(lCounter)` before `End Function` – Trimax Sep 13 '17 at 08:38
  • 1
    @Trimax - this will not help at all. See what I learned literally yesterday - https://stackoverflow.com/questions/46171685/circular-error-in-vba-user-defined-function/46171873?noredirect=1#comment79338343_46171873 – Vityata Sep 13 '17 at 08:44
  • 1
    Your code works for me, so I suspect it is the parameters you are passing, or the data you are processing. Can you update the question to show how you call the UDF. – YowE3K Sep 13 '17 at 08:50
  • if `rColNum` is a `Range`, then `lColNumOff = rColNum` should be either `Set lColNumOff = rColNum` , or `lColNumOff = rColNum.Column` – Shai Rado Sep 13 '17 at 08:55
  • 2
    @ShaiRado Not if it is intended to be a cell that specifies a number indicating the number of columns to offset by. (I originally tried passing `1` as that parameter until I realised it was a `Range`, and then had to pass a cell reference for a cell containing the number 1.) – YowE3K Sep 13 '17 at 08:56
  • Just to double check, the function is in a module, and not somewhere else, right? – Vityata Sep 13 '17 at 09:32
  • Yes it is in a module. Thanks – MistyBlue Sep 13 '17 at 09:34
  • Then probably you are giving to `rKRM` or `rColNum` more than 1 cell in the range? – Vityata Sep 13 '17 at 09:38
  • It would make it easier to help if you would upload a sample workbook that demonstrates the problem, including how you are calling the function, and post a link here. – Ron Rosenfeld Sep 13 '17 at 12:01
  • 2
    Ok So it was an error with the data I was referencing in the names of the people in question. There were a couple of cells that had #NA in the full data set. Thanks Everyone!!! – MistyBlue Sep 13 '17 at 23:54

0 Answers0