searching a cell by format

[Se7en]

hi all

i wanna find the next cell with 3 alphas and 3 numbers in that order in the same cell, it has to be searched in the column.

How must that be done?

 

[just_jon]

Welcome to MrExcel -- will the cell values be more than 6 characters long, and - if so - will your target be bound to the 1st 6 characters only or anywhere in the string?

 

[Joe Was]

Note: I posted this with "If ActiveCell.Address.Column = 2 Then" The code below corrects this error!

Sub myFindFormat()
'Run from standard sheet module, like: Sheet1.
'Find next cell from cursor with my format!
Dim myVar, myN1, myN2, myN3, myA1, myA2, myA3, myLen, myFlag

myFlag = 0
'Force search to column "B" or [2].
If ActiveCell.Column = 2 Then
mySel = ActiveCell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
Else
mySel = "B1"
End If

'Find my format!
For Each c In Sheets("Sheet1").Range(mySel & ":B500")
myVar = c.Value

myA1 = IsNumeric(Left(myVar, 1))
myA2 = IsNumeric(Mid(myVar, 2, 1))
myA3 = IsNumeric(Mid(myVar, 3, 1))
myN1 = IsNumeric(Mid(myVar, 4, 1))
myN2 = IsNumeric(Mid(myVar, 5, 1))
myN3 = IsNumeric(Right(myVar, 1))
myLen = Len(myVar)

If (myA1 = False And myA2 = False And myA3 = False And _
myN1 = True And myN2 = True And myN3 = True And _
myLen = 6) Then
myFlag = 0
c.Select
MsgBox "Found at: " & c.Address
Exit For
Else
myFlag = myFlag + 1
End If
Next c
If myFlag >= 1 Then
MsgBox "Not Found!"
End If
End Sub