Find an Alphanumeric in a column

[ogopogo5]

Still there a way to write in VB to find a alphanumeric in column. I.E.
Columns C5, D5, E5, F5 all have numbers but in G5 is alphanumeric {for
example PT22}. It will skip the cells that have the number but will find
and select the alphanumeric.

Ogopogo5

*** Sent via Developersdex http://www.developersdex.com ***

 

[Chip Pearson]

You can adapt the following code.

Sub AAA()
Dim R As Range
Set R = Range("A1") '<<< SET TO YOUR CELL

Do Until False
If Len(R.Text) > 0 Then
If IsNumeric(R.Text) = False Then
Exit Do
Else
If R.Column < R.Worksheet.Columns.Count Then
Set R = R(1, 2)
Else
Set R = Nothing
Exit Do
End If
End If
Else
If R.Column < R.Worksheet.Columns.Count Then
Set R = R(1, 2)
Else
Set R = Nothing
Exit Do
End If
End If
Loop

If R Is Nothing Then
MsgBox "No non-blank alpha numeric cell found."
Else
MsgBox "Alpha Cell: " & R.Address
End If
End Sub

The variable R will be set to the first non-blank, non-numeric cell.
If no such cell is found by the last column, R has the value Nothing,
indicating that no cell was found.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ogopogo5]

Thank you very much Chip. Instead of the MsgBox, is there a way to
select the cell itself?

Ogopogo5

*** Sent via Developersdex http://www.developersdex.com ***

 

[ogopogo5]

Hi Chip, please ignore my previous reply. I was able to make it select
by changing MsgBox "Alpha Cell: " & R.Address to R.Select. Thank very
much for your help

Ogopogo5

*** Sent via Developersdex http://www.developersdex.com ***