Excel VBA - If statement returning false returns #value

1
votes

I'm working on a bigger Excel Macro, but have simplified my code a bit and found what my issue is. In the if statement below, I can change the rng to a cell that makes the statement true, and it works fine. But if I change it to a cell (or a cell within a range) that will return false, it returns the #value error. I'm pretty new to VBA, so what am I doing wrong?

Function count_same(column As Range, row As Range)

Dim rng As Range
Dim cell As Range
Dim result As Long
result = 0

Set rng = Worksheets("Install together 2").Range("f10")

For Each cell In rng.Cells

If WorksheetFunction.IsNumber(WorksheetFunction.Search(row.Value, cell.Value)) Then
    result = result + 1
End If

Next cell

count_same = result

End Function
3
vbaexcel
can you provide a working and non working example, a cell is a range object. – Sorceri
The one above is working, because cell f10 contains a match of what I'm searching for. But if I change that range to f11, or even "f10:f11", it throws #value because f11 does not contain what I'm searching for. I'd just like it to skip over f11 if it does not contain the text. – thuetten
Try this instead. if instr(1,cell,row) > 0 then – Zerk
Why would he need to skip an error? That would be terribly poor advice for a new programmer. On Error Resume Next should be avoided unless absolutely neccesary. – Brandon Barney
From MSDN, regarding the SEARCH method: "If find_text is not found, the #VALUE! error value is returned." You might have to wrap it in an IfError method to get what you want, or rewrite in a different way. Can you reword what you're trying to achieve with this? – Krypton

3 Answers

0
votes

Try something like this instead of my previous answer. If I understand your needs correctly, this should do the trick. It takes an input range (that you want to search through), and an inputrange with the searchvalue. It then returns the instances of that substring within the input range.

At the top of the code I have included Application.Volatile which forces it to recalculate when the sheet changes. You can comment this out.

Function count_same(InputRange As Range, RowValue As Range, ColValue as Range)
    Application.Volatile

    Dim result As Long
    result = 0

    Dim cell As Range
    For Each cell In InputRange.Cells
        If InStr(1, RowValue.Value, cell.Value, vbTextCompare) > 0 And InStr(1, ColValue.Value, cell.Value, vbTextCompare) > 0 Then
            result = result + 1
        End If
    Next cell

    count_same = result
End Function
0
votes

Try something like this:

Function count_same(column As Range, row As Range)

Dim rng As Range
Dim cell As Range
Dim result As Long
result = 0

Set rng = Worksheets("Install together 2").Range("f10")

For Each cell In rng.Cells
    If Not IsError(WorksheetFunction.Search(Row.Value, cell.Value)) Then
        If WorksheetFunction.IsNumber(WorksheetFunction.Search(row.Value, cell.Value)) Then
            result = result + 1
        End If
    End If

Next cell

count_same = result

End Function

The practice to learn from this is that we can anticipate a potential error. What we then do is check the inverse of the IsError function. IsError will return TRUE if it is an error. We only want to run the Worksheet function if it isnt an error. By doing If Not IsError we are effectively saying 'If this is not an error then'.

As you learn VBA, try to prevent errors instead of using On Error Resume Next, or GoTo since these work well enough when you're getting started, but become impossible to use when you get further along (and you'll curse yourself every time you see a GoTo or an On Error statement).

0
votes

you can use on error resume next and put some error checking in. you also have column as Range but don't ever use it in the method so you can remove it.

Public Function count_same(row As Range)
On Error Resume Next
Dim rng As Range
Dim cell As Range
Dim result As Long
result = 0

Set rng = ThisWorkbook.Sheets("Install together 2").Range("F10")

For Each cell In rng.Cells

    If WorksheetFunction.IsNumber(WorksheetFunction.Search(row.Value, cell.Value)) Then
        If Err.Number <> 0 Then
            Err.Clear
        Else
            result = result + 1
        End If
    End If

Next cell

count_same = result

End Function