Comparing cell contents via VB

[Guest]

Hello,

I was wondering if anyone could suggest a way of writing code in VB to
compare the texts in cells.

For example if, in Column A, going down from row 1 to 4, the values/strings
were;

Cell A1 had the word Apple
Cell A2 had the word Peachy
Cell A3 had the word Plopped
Cell A4 had the word Hate

If I wanted to know how many "p"s appeared in each of the words, and the
answer went in the adjacent column, what code could i use?

So the end result would then have column B as,

Cell B1 has the result 2
Cell B2 has the result 1
Cell B3 has the result 3
Cell B4 has the result 0


Many thanks for your help - please make the code as simple or as complicated
as you will, or as long or as short as you see fit. Thanks

Rajen

 

[meatshield]

Hello,

I was wondering if anyone could suggest a way of writing code in VB to
compare the texts in cells.

For example if, in Column A, going down from row 1 to 4, the values/strings
were;

Cell A1 had the word Apple
Cell A2 had the word Peachy
Cell A3 had the word Plopped
Cell A4 had the word Hate

If I wanted to know how many "p"s appeared in each of the words, and the
answer went in the adjacent column, what code could i use?

So the end result would then have column B as,

Cell B1 has the result 2
Cell B2 has the result 1
Cell B3 has the result 3
Cell B4 has the result 0

Many thanks for your help - please make the code as simple or as complicated
as you will, or as long or as short as you see fit. Thanks

Rajen

You don't need VBA to do what you're proposing, but you can use it if
you like.
If you put the following formula in cell B1, it'll return the same
result:
LEN(A1)-LEN(SUBSTITUTE(A1,"p",""))

In VBA, you could use:
Sub Find_Letter
Dim strletter as string
Dim rng as range

strletter = "p"
For each rng in selection
rng.offset(0,1).value = len(rng.value)-
len(replace(rng.value,strletter,vbnullstring))
Next rng

End Sub

 

[meatshield]

Hello,

I was wondering if anyone could suggest a way of writing code in VB to
compare the texts in cells.

For example if, in Column A, going down from row 1 to 4, the values/strings
were;

Cell A1 had the word Apple
Cell A2 had the word Peachy
Cell A3 had the word Plopped
Cell A4 had the word Hate

If I wanted to know how many "p"s appeared in each of the words, and the
answer went in the adjacent column, what code could i use?

So the end result would then have column B as,

Cell B1 has the result 2
Cell B2 has the result 1
Cell B3 has the result 3
Cell B4 has the result 0

Many thanks for your help - please make the code as simple or as complicated
as you will, or as long or as short as you see fit. Thanks

Rajen

You don't need VBA to do what you're proposing, but you can use it if
you like.
If you put the following formula in cell B1, it'll return the same
result:
LEN(A1)-LEN(SUBSTITUTE(A1,"p",""))

In VBA, you could use:
Sub Find_Letter
Dim strletter as string
Dim rng as range

strletter = "p"
For each rng in selection
rng.offset(0,1).value = len(rng.value)-
len(replace(rng.value,strletter,vbnullstring))
Next rng

End Sub

 

[Guest]

That's really good - I'm gonna mess around with it now.

Coming from a QBasic background (ancient), some of the terms in the code I
am unfamiliar with, but I'll play and see what they do.

Looks good though - Any other suggestions from anyone are welcome

Rajen

 

[Guest]

Your formula code "Substitute" is brilliant. Never used it before, but I
understand the logic. Brilliant way of doing it.

 

[meatshield]

Your formula code "Substitute" is brilliant. Never used it before, but I
understand the logic. Brilliant way of doing it.

I wish I could take credit for thinking of it. =) I came across that
little trick before and filed it away because I thought it was pretty
clever.