Excel - Macro to find/replace cells containing a number and letter

Hi,

I am trying to find cells that contain a number and the letter A (such as
There are other cells that contain only letters (like "CAT") or only
numbers (like "16") that I do not want to affect.  Any advice would be
appreciated.

Thanks!




--
mb6g

hi mb6g,


Sub Macro1()
Dim x1 As Boolean, x2 As Boolean, x3 As Boolean
Dim i As Integer, w As String, c As Range

For Each c In Range("A1:A10")  'range of cells to be determined
For i = Len(c) To 1 Step -1
On Error Resume Next
x1 = Mid(c, i, 1) = "A"
If Err.Number <> 0 Then Err.Clear: x1 = False
x2 = IsNumeric(Mid(c, i - 1, 1))
If Err.Number <> 0 Then Err.Clear: x2 = False
x3 = IsNumeric(Mid(c, i - 2, 1))
If Err.Number <> 0 Then Err.Clear: x3 = False

If x1 And x2 And x3 Then
w = Mid(c, i - 2, 1) & Mid(c, i - 1, 1) & Mid(c, i, 1)
c.Replace What:=w, Replacement:="20A", LookAt:=xlPart
End If
Next
Next
End Sub

--
isabelle



Le 2012-06-08 16:25, mb6g a ?crit :

Hi Isabelle,

Thanks, that worked great.  I took out the x3 requirement, since some of
my numbers only have one digit (such as 2A), and I simplified the
replace line to:

If x1 And x2 Then c.Replace What:="A", Replacement:="", LookAt:=xlPart.

Thanks again for your help!

Mary


isabelle;1602529 Wrote:




--
mb6g