Using Wildcard in VBA IF
 

Thanks for any assistance you can provide. I have looked and can't seem to
find the answer and I don't think it should be that hard.

I have a column that has some text cells that start with "PO....". I would
like to replace all cells in that column with "" or delete the contents. I
thought just replacing with "" would be easiest. I have tried using * and ~
as wildcards. Neither is working

Here is what I was trying to use:

Range("a2:a98").Replace What:="P*", Replacement:=""
--
Thanks - K

Using Wildcard in VBA IF
 

Sub kristen()
Set r = Range("A2:A98")
For Each rr In r
If Left(rr.Value, 2) = "PO" Then
rr.Value = ""
End If
Next
End Sub

--
Gary''s Student - gsnu200800

Using Wildcard in VBA IF
 

I don't see anything wrong with the code, except maybe the sheet should be
specified. I tried it with different case just to be sure the Replace
function is not case sensitive and it worked regardless of case.

"Kristen" wrote:

Thanks for any assistance you can provide. I have looked and can't seem to
find the answer and I don't think it should be that hard.

I have a column that has some text cells that start with "PO....". I would
like to replace all cells in that column with "" or delete the contents. I
thought just replacing with "" would be easiest. I have tried using * and ~
as wildcards. Neither is working

Here is what I was trying to use:

Range("a2:a98").Replace What:="P*", Replacement:=""
--
Thanks - K

Using Wildcard in VBA IF
 

Yes, the posted statement worked for me also. I would note, though, that if
"PO" appeared anywhere in the text, then the PO and all text following it
gets deleted. For example, if the cell contains abcPOdef, then after
executing that line, the cell contains abc only.

Rick


"JLGWhiz" wrote in message
...
I don't see anything wrong with the code, except maybe the sheet should be
specified. I tried it with different case just to be sure the Replace
function is not case sensitive and it worked regardless of case.

"Kristen" wrote:

Thanks for any assistance you can provide. I have looked and can't seem
to
find the answer and I don't think it should be that hard.

I have a column that has some text cells that start with "PO....". I
would
like to replace all cells in that column with "" or delete the contents.
I
thought just replacing with "" would be easiest. I have tried using *
and ~
as wildcards. Neither is working

Here is what I was trying to use:

Range("a2:a98").Replace What:="P*", Replacement:=""
--
Thanks - K

Using Wildcard in VBA IF
 

The only way I can replicate your problem is if the Text that ou describe is
there as the result of a formula. If it is a formula returning "PO..." then
you can not replace the value... Otherwise the code works for me...
--
HTH...

Jim Thomlinson


"Kristen" wrote:

Thanks for any assistance you can provide. I have looked and can't seem to
find the answer and I don't think it should be that hard.

I have a column that has some text cells that start with "PO....". I would
like to replace all cells in that column with "" or delete the contents. I
thought just replacing with "" would be easiest. I have tried using * and ~
as wildcards. Neither is working

Here is what I was trying to use:

Range("a2:a98").Replace What:="P*", Replacement:=""
--
Thanks - K

Using Wildcard in VBA IF
 

Just a quick note... this code is case sensitive and only works if the
leading PO is upper case whereas the code Kristen first posted would work no
matter what the PO's case was. I don't know if that matters to Kristen or
not, I just thought I would mention it. As for making the code case
insensitive (if that is what you want Kristen), chant the If..Then statement
in Gary''s Student's code to this...

If UCase(Left(rr.Value, 2) = "PO" Then

or to this...

If rr.Value Like "[Pp][Oo]*" Then

You can use either one as they both work the same.

Rick


"Gary''s Student" wrote in message
...
Sub kristen()
Set r = Range("A2:A98")
For Each rr In r
If Left(rr.Value, 2) = "PO" Then
rr.Value = ""
End If
Next
End Sub

--
Gary''s Student - gsnu200800

Using Wildcard in VBA IF
 

There is one other possibility that could cause the anomaly. If you have a
space before the data then it would not recognze the "P" as the first
character. So, try

Dim rng As Range
For Each rng In Range("A2:A98")
If UCase(LTrim(rng)) Like "P*" Then
rng = ""
End If
Next


"Kristen" wrote:

Thanks for any assistance you can provide. I have looked and can't seem to
find the answer and I don't think it should be that hard.

I have a column that has some text cells that start with "PO....". I would
like to replace all cells in that column with "" or delete the contents. I
thought just replacing with "" would be easiest. I have tried using * and ~
as wildcards. Neither is working

Here is what I was trying to use:

Range("a2:a98").Replace What:="P*", Replacement:=""
--
Thanks - K

Using Wildcard in VBA IF
 

This helped. Can you tell me if "rr" means anything particular?

I also continued to look and found that I could use "Like" and that seemed
to work.

If convertcell Like "P*" Then GoTo TextCell

I used like to just direct the program to skip over those cells. I have not
fully tested this, but I will do some more research.
--
Thanks - K


"Gary''s Student" wrote:

Sub kristen()
Set r = Range("A2:A98")
For Each rr In r
If Left(rr.Value, 2) = "PO" Then
rr.Value = ""
End If
Next
End Sub

--
Gary''s Student - gsnu200800

Using Wildcard in VBA IF
 

rr is an individual cell in the range r.

Basically, we are looping over all the cells in the range and fixing them if
the criteria is met.
--
Gary''s Student - gsnu200800


"Kristen" wrote:

This helped. Can you tell me if "rr" means anything particular?

I also continued to look and found that I could use "Like" and that seemed
to work.

If convertcell Like "P*" Then GoTo TextCell

I used like to just direct the program to skip over those cells. I have not
fully tested this, but I will do some more research.
--
Thanks - K


"Gary''s Student" wrote:

Sub kristen()
Set r = Range("A2:A98")
For Each rr In r
If Left(rr.Value, 2) = "PO" Then
rr.Value = ""
End If
Next
End Sub

--
Gary''s Student - gsnu200800