Find an Alphanumeric in a column

  • Thread starter Thread starter ogopogo5
  • Start date Start date
O

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
 
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)
 
Thank you very much Chip. Instead of the MsgBox, is there a way to
select the cell itself?

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
 
Back
Top