Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code needs your help
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code needs your help
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code needs your help
It was the special cells designation that was preventing me from finding
blanks, originally! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
How to assign same code inside Option button code space ?? | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |