Find an Alphanumeric in a column
 

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 ***

Find an Alphanumeric in a column
 

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)


On Mon, 20 Oct 2008 13:38:28 -0700, ogopogo5
wrote:

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 ***

Find an Alphanumeric in a column
 

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 ***

Find an Alphanumeric in a column
 

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 ***