# finding number or "." in a range

#### krice1974

##### Active Member
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!!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Code:
``````    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``````

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.

Code:
``````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``````

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.

Change

'Do Stuff

in mine to

cell.EntireRow.Delete

Try

Code:
``````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``````

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?

Replies
1
Views
1K
Replies
1
Views
581
Replies
0
Views
657
Replies
1
Views
1K
Replies
1
Views
260

1,203,675
Messages
6,056,683
Members
444,882
Latest member
cboyce44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back