CODEWITHSUNDEEP
vbaexcelruntime-error
user3487972
user3487972

Reputation: 3

vba runtime error 424 object required - string indexing

I looked at the other links and none seem to help me out. I am writing code for a program that will count all the commas in a phrase. I am not new to programming but I am new to VBA.

Sub examp()

Dim s As String

Dim i, my_c As Integer

i = 0

s = ",jkqk;j,oiheqfjnq;ef,jwhef;ur,jwefun;jwkbnf," '<-------arbitrary, however, when I tried to make it input from a textbox it gave me error 424 as well, so I just defined it as random chars with commas

While i < Len(s)

    For i = 0 To Len(s) - 1

    j = s.Chars(i)  <----------------------------------Error occurs here
        If j = "," Then
            my_c = my_c + 1
    End If
    Next i
Wend

Count.Text = "my_c"

End Sub

Upvotes: 0

Views: 955

Answers (2)

L42
L42

Reputation: 19727

Or you can try this:

Sub test()

Dim s As String
Dim c
Dim my_c As Long

s = ",jkqk;j,oiheqfjnq;ef,jwhef;ur,jwefun;jwkbnf,"
c = Split(s, ",")
my_c = UBound(c)
Debug.Print my_c

End Sub

Upvotes: 2

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

  1. change j = s.Chars(i) to j = Mid(s,i,1)
  2. in line Dim i, my_c As Integer only my_c is Integer, but i is Variant. You should declare each variable explicitly: Dim i As Integer, my_c As Integer
  3. not sure what exactly is your Count (maybe textbox), but use Count.Text = my_c without quotes.
  4. also I can't undersand why do you use two loops? While i < Len(s) is odd.
  5. For i = 0 To Len(s) - 1 should be For i = 1 To Len(s)

If you want to count commas, there is more efficient way:

Dim s As String
Dim my_c As Integer
s = ",jkqk;j,oiheqfjnq;ef,jwhef;ur,jwefun;jwkbnf,"
my_c = Len(s) - Len(Replace(s, ",", ""))

Upvotes: 3

Related Questions