Home |
Search |
Today's Posts |
#1
|
|||
|
|||
excel input 0 when cell blank or deleted by key or spacebar
Need to write a macro that will update sheet if entry error. Need to replace
entry error with 0 so formulas will work. Blank cell gives error msg. Macro will be run to correct. I am useing excel 2000. Have tried =if(b1="",0,) also =if(and(b1<"",b1=0),"n/a","") next =if(iserror(org form),"",(orig form)) |
#2
|
|||
|
|||
excel input 0 when cell blank or deleted by key or spacebar
Record a macro when you select your range
edit|replace what: (leave empty) with: 0 replace all Curt wrote: Need to write a macro that will update sheet if entry error. Need to replace entry error with 0 so formulas will work. Blank cell gives error msg. Macro will be run to correct. I am useing excel 2000. Have tried =if(b1="",0,) also =if(and(b1<"",b1=0),"n/a","") next =if(iserror(org form),"",(orig form)) -- Dave Peterson |
#3
|
|||
|
|||
excel input 0 when cell blank or deleted by key or spacebar
Thanks much. Do you have any idea why it will only do two columns? I have re
written the order still same. Is there a limit on this function. Columns are 12 rows not together. At 68 it sure is fun learning about this and what it will do "Dave Peterson" wrote: Record a macro when you select your range edit|replace what: (leave empty) with: 0 replace all Curt wrote: Need to write a macro that will update sheet if entry error. Need to replace entry error with 0 so formulas will work. Blank cell gives error msg. Macro will be run to correct. I am useing excel 2000. Have tried =if(b1="",0,) also =if(and(b1<"",b1=0),"n/a","") next =if(iserror(org form),"",(orig form)) -- Dave Peterson |
#4
|
|||
|
|||
excel input 0 when cell blank or deleted by key or spacebar
Edit|Replace only works against the used range.
If you hit ctrl-end, is the selected cell to the right or left of that not-included column. If it's to the left, then that's a problem. If it's to the right, then I'd bet your "empty" cells aren't really empty. Maybe you have a spacebar (or two in those cells)??? Curt wrote: Thanks much. Do you have any idea why it will only do two columns? I have re written the order still same. Is there a limit on this function. Columns are 12 rows not together. At 68 it sure is fun learning about this and what it will do "Dave Peterson" wrote: Record a macro when you select your range edit|replace what: (leave empty) with: 0 replace all Curt wrote: Need to write a macro that will update sheet if entry error. Need to replace entry error with 0 so formulas will work. Blank cell gives error msg. Macro will be run to correct. I am useing excel 2000. Have tried =if(b1="",0,) also =if(and(b1<"",b1=0),"n/a","") next =if(iserror(org form),"",(orig form)) -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
excel input 0 when cell blank or deleted by key or spacebar
Yes there would be a spacebar or a delete entyry in cell. Listed all ranges
in macro guess limit passed Thanks much will hav to try a different approach gday "Dave Peterson" wrote: Edit|Replace only works against the used range. If you hit ctrl-end, is the selected cell to the right or left of that not-included column. If it's to the left, then that's a problem. If it's to the right, then I'd bet your "empty" cells aren't really empty. Maybe you have a spacebar (or two in those cells)??? Curt wrote: Thanks much. Do you have any idea why it will only do two columns? I have re written the order still same. Is there a limit on this function. Columns are 12 rows not together. At 68 it sure is fun learning about this and what it will do "Dave Peterson" wrote: Record a macro when you select your range edit|replace what: (leave empty) with: 0 replace all Curt wrote: Need to write a macro that will update sheet if entry error. Need to replace entry error with 0 so formulas will work. Blank cell gives error msg. Macro will be run to correct. I am useing excel 2000. Have tried =if(b1="",0,) also =if(and(b1<"",b1=0),"n/a","") next =if(iserror(org form),"",(orig form)) -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
excel input 0 when cell blank or deleted by key or spacebar
If there's a single space bar in each of those cells, you could
select the range edit|Replace what: (spacebar) with: (leave empty) (make sure "match entire cell contents" is checked) replace all. In fact, if you knew that all the cells that looked empty contained no more than say 6 space characters, you could keep doing these edit|replaces. If you're not sure, you could look through all the cells in the selection and clean them up. Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Set myRng = Nothing On Error Resume Next Set myRng = Intersect(Selection, _ Selection.Cells.SpecialCells(xlCellTypeConstants)) On Error GoTo 0 If myRng Is Nothing Then MsgBox "No constants in selection" Exit Sub End If For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then myCell.Value = 0 End If Next myCell End Sub Curt wrote: Yes there would be a spacebar or a delete entyry in cell. Listed all ranges in macro guess limit passed Thanks much will hav to try a different approach gday "Dave Peterson" wrote: Edit|Replace only works against the used range. If you hit ctrl-end, is the selected cell to the right or left of that not-included column. If it's to the left, then that's a problem. If it's to the right, then I'd bet your "empty" cells aren't really empty. Maybe you have a spacebar (or two in those cells)??? Curt wrote: Thanks much. Do you have any idea why it will only do two columns? I have re written the order still same. Is there a limit on this function. Columns are 12 rows not together. At 68 it sure is fun learning about this and what it will do "Dave Peterson" wrote: Record a macro when you select your range edit|replace what: (leave empty) with: 0 replace all Curt wrote: Need to write a macro that will update sheet if entry error. Need to replace entry error with 0 so formulas will work. Blank cell gives error msg. Macro will be run to correct. I am useing excel 2000. Have tried =if(b1="",0,) also =if(and(b1<"",b1=0),"n/a","") next =if(iserror(org form),"",(orig form)) -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Look for change next blank cell in Range | Excel Worksheet Functions | |||
Cell set to wrap text and blank line -- fix? | Excel Discussion (Misc queries) | |||
how do i create a blank cell in excel using a formula | Excel Worksheet Functions |