View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
NickHK NickHK is offline
external usenet poster
 
Posts: 4,391
Default Code needs your help

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" wrote in message
...
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.