finding number or "." in a range

[krice1974]

Hey there all. For the life of me I can't remember, or find in searches, the syntax for a "for each cell in range, next cell" that will 1) find if the cell's value contains a "." or 2) contains a numeric character within a string (ex: RT3)

Thank you much!!

 

[xld]

    For Each cell In Range("A2:A20")
    
        fNumber = False
        
        For i = 1 To Len(cell.Value)
        
            If IsNumeric(Mid$(cell.Value, i, 1)) Then
            
                fNumber = True
                Exit For
            End If
        Next i
        
        If InStr(cell.Value, ".") > 0 Or fNumber Then
        
            'Do stuff
        End If
    Next cell

 

[VoG]

There's probably a much smarter way using Regular Expressions but this will put a . (for .) or N (for number) in column B if the condition is satisfioed in column A.

Sub rice()
Dim LR As Long, i As Long, j As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        If .Value <> "" Then
            If InStr(.Value, ".") > 0 Then
                .Offset(, 1).Value = "."
            Else
                For j = 1 To Len(.Value)
                    If IsNumeric(Mid(.Value, j, 1)) Then
                        .Offset(, 1).Value = "N"
                        Exit For
                    End If
                Next j
            End If
        End If
    End With
Next i
End Sub

 

[krice1974]

Thanks, mates... I should have mentioned it in the first post, but I want to ".row.entirerow.delete" when I find either condition. Simple stuff. Sorry if this changes things!! I managed a similar bit of code a while back and each condition was a short line. So close, yet so far... thanks again.

 

[xld]

Change

'Do Stuff

in mine to

cell.EntireRow.Delete

 

[VoG]

Try

Sub rice()
Dim LR As Long, i As Long, j As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("A" & i)
        If .Value <> "" Then
            If InStr(.Value, ".") > 0 Then
                Rows(i).Delete
            Else
                For j = 1 To Len(.Value)
                    If IsNumeric(Mid(.Value, j, 1)) Then
                        Rows(i).Delete
                        Exit For
                    End If
                Next j
            End If
        End If
    End With
Next i
End Sub

 

[krice1974]

Thanks again. Xld, funny but I asked for it lol

I thought something very roughly (key words) like this was possible:

if instr(.value, ".") or if instr(is numeric) then
row.entirerow.delete

but I guess not

Can you educate me on what the "mid" means?