#VALUE error with Excel VBA Function

Question

In my Excel spreadsheet I have two columns.

A contains strings with the values 'Yes', 'No' or 'Maybe'.
B contains strings with a year in.

I need a function to determine the number of occurrences of a year in column B, where the equivalent value in column A is 'Yes'.

I currently have the following code:

Function CountIfYearAndValue(Rng As Range, YNM As String, Year As String) As Integer
    Dim count As Integer
    count = 0

    For Each c In Rng.Cells
        If (StrComp(Abs(c.Value), Year, vbTextCompare) = 0) And (StrComp(Cells(c.Row, A), YMN, vbTextCompare) = 0) Then count = count + 1
    Next

    CountIfYearAndValue = count
End Function

The idea of this code is that we iterate through every cell in the range given (a range on column B) and check if the year is equal to the Year parameter. And if the equivalent cell on column A is equal to the YNM parameter we increment the count variable.

For some reason this code does not work when I use the following parameter:

=CountIfYearAndValue('Years'!B1:B7,"Yes","Year 7")

It just does the #VALUE error and refuses to display any outcome.

Any help would be much appreciated.

Edit: All of the values in both cells are on of an unformatted datatype ('General') and no cells are blank.

Kezz101, @mehow is absolutely right it's better to use "COUNTIF". But, if you're interesing why your solution doesn't work - it's because of 1) Cells(c.Row, A). You should add quotes to A : Cells(c.Row, "A") and 2) in functions parameters you are using YNM As String, but in code you are using StrComp(Cells(c.Row, A), YMN, vbTextCompare). Note, there is YMN and YNM. I suggest you to use Option Explicit to avoid such kind of errors — Dmitry Pavliv
– Dmitry Pavliv, Commented Feb 17, 2014 at 16:16
@simoco Thanks for the response! I have now used the COUNTIF function, but I'm interested to see why it still doesn't work. I implemented your changes and it still doesn't work with the same #VALUE error. — Kezz
– Kezz, Commented Feb 17, 2014 at 19:25
can't figured it out, because I haven't seen your updated code, but with this changes your function works for me — Dmitry Pavliv
– Dmitry Pavliv, Commented Feb 17, 2014 at 19:27
thanks @simoco I have added those you've pointed out and a few more, good job. — user2140173
– user2140173, Commented Feb 17, 2014 at 20:24

score 5 · Accepted Answer · 2014-02-18 08:01:13Z

5

It sounds like you are reinventing the wheel... There already is a built in function (advantage: being much faster than a UDF) that does exactly what you are after. It is called COUNTIFS()

All YESes for Year 7 in rows 1 to 10.

=COUNTIFS(B1:B10, "Year 7",A1:A10, "Yes")

I just had a quick look at your code and I think there are possibly a few reasons why your original code is not working as expected.

YNM is a valid column name therefore it should not be used as a variable name. You should avoid naming your variables like that - give it a more meaningful name
YNM != YMN as you had it in your code (see function definition and then the misspelled version in the StrComp() function)
Year is a valid VBA built in function, therefore once again you should avoid using it as a variable name as you're exposing yourself to a naming collision.
Add Option Explicit at the top of your module. This requires you to Dimension all you variables. It's always recommended for many many reasons.
rng variable is of Range type therefore you do not need to explicitly add the .Cells property to it. Even though it may help in some cases - at a bit more advanced level you may face some runtime type compatibility issues. ( runtime may convert your rng Range variable to a 2D array etc )
Added an explicit conversion in the second StrComp() function around the c.Offset(0, -1) as you don't want the runtime to (rare but still possible) convert your Yes to a Boolean data type. Explicit conversion to a String just gives you that extra protection ;p (lol)

therefore, something like this returns the correct value

Function CountIfYearAndValue(rng As Range, choice As String, myYear As String) As Long
    Dim count As Long
    count = 0

    Dim c As Range
    For Each c In rng
        If (StrComp(c, myYear, vbTextCompare) = 0) And (StrComp(CStr(c.Offset(0, -1)), choice, vbTextCompare) = 0) Then
            count = count + 1
        End If
    Next c

    CountIfYearAndValue = count
End Function

Right, I hope this helps you understand bits and pieces :) any questions please leave a comment

edited Feb 18, 2014 at 8:01

answered Feb 17, 2014 at 16:11

user2140173

Sign up to request clarification or add additional context in comments.

3 Comments

Kezz Over a year ago

This works perfectly, but still throws a #VALUE error when it encounters empty cells. Anyway to fix this other than filling in the empty cells?

Dmitry Pavliv Over a year ago

@Kezz101, are you using ranges with the same rows count (e.g. B1:B10 and A1:A10 has the same rows count)?

user2140173 Over a year ago

@Kezz101 hey I have edited my answer with some extra explanations for you. If you're happy with it consider accepting it, cheers

Collectives™ on Stack Overflow

#VALUE error with Excel VBA Function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related