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.

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

"justme" <(E-Mail Removed)> wrote in message
news:8BABE8BC-F6B3-4D0C-963F-(E-Mail Removed)...
> 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.

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.

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