macro to parse and count the entities in a field

[kunalg]

Hi

I have to write a macro to count the number of items in one field and display the count in other field.

Suppose

A1 has a4,a2,a3,a5,a6

A2 shows 5

the logic behind this is eliminating , and showing the count... or comma +1. if there is comma before or after this string, eliminate the comma at the end and the beginning and display the count.

I would appreciate any help in this as I am new to macros....


Thanks

[zbor]

Maybe something like this without a macro:

=LEN(MID(A1,2,LEN(A1)-2))-LEN(SUBSTITUTE(MID(A1,2,LEN(A1)-2),",",""))+1

[kunalg]

hi thanks for this.


Please let me know if it is more than one field, then how should I proceed, that is


I have to ask the user the range and then insert the answer in the corresponding range


so suppose the user says that the data is in cells c7 to c37 and to display it into e5 to 35


I have created a userform, and with that I am collecting the data, so the value from the textbox will go into the formula,

the only problem I am facing is with more than one cell


I don't know how to select a cell/range and then insert the formula


Please let me know

thanks
Kunal

[zbor]

You can use in macro:

Range("E5:E35") = "=LEN(MID(C7,2,LEN(C7)-2))-LEN(SUBSTITUTE(MID(C7,2,LEN(C7)-2),",",""))+1"

Does that helps?

[kunalg]

its not only c7. read carefully, its c7:c37

[zbor]

Did you try it?

[kunalg]

I have
c1: 1,2,3,4,5
c2: 2,3,4,5,6
c3: 3,4,5,6,7
c4: 2,3,4
c5: 1,5,6,8
c6: 2,3,4,5,6,7,9,8

So according to the formula you have given

I create a macro

Sub count()

Range("E1:E6") = LEN(MID(C1,2,LEN(C1)-2))-LEN(SUBSTITUTE(MID(C1,2,LEN(C1)-2),",",""))+1

End Sub

gives me compile error

Sub or function not defined which highlights SUBSTITUTE

Please help

thanks
Kunal

[zbor]

For the third time...

Sub count()
Range("E1:E6") = "=LEN(MID(C1,2,LEN(C1)-2))-LEN(SUBSTITUTE(MID(C1,2,LEN(C1)-2),",",""))+1"
End Sub

[kunalg]

Compile error

Expected: end of statement



I copy pasted as you suggested

Thanks

[zbor]

Try now:

Sub count()
Range("E1:E6") = "=LEN(MID(C1,2,LEN(C1)-2))-LEN(SUBSTITUTE(MID(C1,2,LEN(C1)-2),"","",""""))+1"
End Sub

[kunalg]

it works great, also could you tell me why you put the extra ""

also if there are multiple comma, we have to eliminate that also

example

the cell is 1,2,3,,4,5,6

it should read 6 and not 7

also if it is

1,2,3,,,4,5,6

it will read 6 and not 8

also if it is
1,2,3,4,,5,6,

it will read 6 and not 7

also what would we do if the separator is whitspace and if we have multiple space, it would remove the extra space and then count

thankyou

[zbor]

Ok.. Check this UDF function...

Function Count_chars(rRange As Range, sstr As String) As Single
Dim i As Variant
Dim m As Variant

m = rRange.Value
Do
i = InStr(m, sstr & sstr)
m = Replace(m, sstr & sstr, sstr)
Loop Until i = 0

Count_chars = Len(Mid(m, 2, Len(m) - 2)) - Len(Replace(Mid(m, 2, Len(m) - 2), sstr, "")) + 1

End Function

Use it as =Count_chars(C1, ",") (or other character you want to test with...

It will consider space as character...sO
2,3,,, ,4 will return 4 (2 3 space 4) and 2,3,,,,4 will return 3 (2 3 4).
is that OK?

[kunalg]

also do you know

if I input the column field from the user




the user inputs something like g1 and g6

I am inputting in userform

input1=userform.textbox1.value
input2=userform.textbox2.value

display1=userform.textbox3.value
display2=userform.textbox4.value

in input1:input2 the data is there to read

and in diplay1:display 2 the data has to be display


that is the formula will now become


Sub count()
Range("display1:display2") = "=LEN(MID(input1,2,LEN(input1)-2))-LEN(SUBSTITUTE(MID(input1,2,LEN(input1)-2),"","",""""))+1"
End Sub


so in this it is giving me global error..

i think excel is not accepting these variables

please help

you have been very helpful which I greatly appreciate!

[kunalg]

if it is 1 2 3 4 it should return 4


in this case space is a separator


it is a different case


if it is 1 2 3

it must return 3


so instead of , it is space

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here