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

On Jun 29, 10:44 am, RajenRajput1
wrote:
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

On Jun 29, 10:44 am, RajenRajput1
wrote:
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

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

"meatshield" wrote:

On Jun 29, 10:44 am, RajenRajput1
wrote:
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

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

"meatshield" wrote:

On Jun 29, 10:44 am, RajenRajput1
wrote:
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

On Jun 29, 11:56 am, RajenRajput1
wrote:
Your formula code "Substitute" is brilliant. Never used it before, but I
understand the logic. Brilliant way of doing it.

"meatshield" wrote:
On Jun 29, 10:44 am, RajenRajput1
wrote:
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

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.