1
Sub Test()    
    Dim countMe As Integer    
    countMe = Sheets("Data").Range("B1").Formula = "=LEN(SUBSTITUTE(B1,""|"",""""))"
    Sheets("Data").Range("C1").Value = countMe    
End Sub

What is wrong with the logic in this code?
C1 value is 0 when it should be 1. In cell B1, I have Test|Test

3 Answers 3

2
Sub Test()

Dim countMe As Integer

  countMe = LEN(SUBSTITUTE(B1,""|"",""""))
  Sheets("Data").Range("C1").Value = countMe

End Sub

C1 should equal 8

2

The countMe line can't have two equal signs, otherwise you're essentially asking Excel if the formula in B1 is equal to "=LEN(SUBSTITUTE(B1,""|"",""""))", which it is not, so it's returnig zero. You can see this by changing the data type of countMe to Variant, and it will default to Boolean and you will get a FALSE instead of a zero.

That's like saying:

countMe = 1 = 0

Which will always result in 0, because 1 <> 0.

You're trying to use application formulas as VBA functions. The proper usage of LEN() would be:

countMe = LEN(Range("B1"))

But for your problem of counting the # of pipes in B1, use the code from this other post: How to find Number of Occurences of Slash from a strings

2

2 ways to count occurrences of "|" in string "Test|Test":

.

1 - Formula

Sub Test1()
    Dim countMe As Long

    With Sheets("Data")

        .Range("C1").Formula = "=LEN(B1)-LEN(SUBSTITUTE(B1,""|"",""""))"    'your formula

        countMe = .Range("C1").Value2   'count will be "1"

        .Range("D1").Value = countMe

    End With
End Sub

.

2 - VBA

Sub Test2()
    Dim countMe As Long

    With Sheets("Data")

        .Range("E1").Value2 = UBound(Split(.Range("B1").Value2, "|"))

        countMe = .Range("F1").Value2   'count will be "1"

        .Range("F1").Value2 = countMe

    End With
End Sub

.

More details about the first one here

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .