Code needs your help

[Guest]

Public Sub FindNonNumericFactories_CLEAN()

Dim myRange As Range, cell As Range
Dim EnterFactory As Variant
On Error Resume Next
With Worksheets("Sheet1")
Set myRange = .Range(.Cells(1, "A"), .Cells(Rows.Count, "A").End(xlUp)) _
..SpecialCells(xlConstants, xlTextValues)
End With
On Error GoTo 0
If myRange Is Nothing Then Exit Sub
For Each cell In myRange

If Not IsNumeric(cell.Value) Or IsEmpty(cell.Value) = "" Then
cell.Select
EnterFactory = InputBox("Enter Factory#: ")
If EnterFactory <> "" Then
ActiveCell.Value = EnterFactory
End If
End If
Next

End Sub
------

This code works(!), but does not do everything I'd like it to do.

1. When it finds a cell , I can click "cancel" to ignore the cell and go to
the next one. This is great, because many times the user will want to leave
the cell as is, but I would like for the cancel button to say "ignore" and to
have a "real" cancel button to exit out of the macro. This may be too much to
ask for, but putting it out there just in case.

2. It seems to find cells with letters, symbols and spaces, but it's not
finding blanks.
Column 1 should contain only a number (positive number).
I'd like to find cells in the first column that:
a) contain any letters, symbols or other non-numeric characters
b) contain a space
c) are blank

Hope there's someone out there who can help me out.
Thanks.

 

[NickHK]

1 - If you need this kind of customisation, make you own userform instead of
using the Inputbox.

2 - You are currently using <IsEmpty(cell.Value) = "">, which seems like a
mix of the 2 similar statements:
IsEmpty(cell.Value) = True
cell.Value = ""

Use one of these

NickHK

 

[Guest]

Thanks, Nick.

The use of IsEmpty *and* ="" was an oversight on my part (duh!). That would
solve that problem. I will look into creating a userform.

 

[Guest]

It was the special cells designation that was preventing me from finding
blanks, originally!