| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for Empty, Null, "", 0
TIA:
I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Empty, Null, "", 0
Mystery cells are always fun.
Say the cell is A1 What does LEN(A!) show ? What does CODE(A1) show? Does nothing appear in both the cell and formula bar? -- Gary's Student "Joel" wrote: TIA: I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Empty, Null, "", 0
Gary:
Len returns 0 Code is #value errror if I change code if len(a1) = 0 then...still doesn't work!!! What else?? Joel "Gary''s Student" wrote: Mystery cells are always fun. Say the cell is A1 What does LEN(A!) show ? What does CODE(A1) show? Does nothing appear in both the cell and formula bar? -- Gary's Student "Joel" wrote: TIA: I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Empty, Null, "", 0
if len(a1) = 0 then
doesn't test the len of cell A1 - are you just writing pseudo code or . . . Both of the results you reported are consistent with an empty cell. Perhaps you have a logic error in your code. -- Regards, Tom Ogilvy "Joel" wrote: Gary: Len returns 0 Code is #value errror if I change code if len(a1) = 0 then...still doesn't work!!! What else?? Joel "Gary''s Student" wrote: Mystery cells are always fun. Say the cell is A1 What does LEN(A!) show ? What does CODE(A1) show? Does nothing appear in both the cell and formula bar? -- Gary's Student "Joel" wrote: TIA: I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Empty, Null, "", 0
Here's the code:
Sub OpnFiles() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object except = InputBox("Enter Exceptions Value") Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder("c:\Copper") 'change directory For Each objFile In objFolder.Files If objFile.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name 'do whatever ' Worksheets("qrycndataall").Activate Range("A2").Select If IsEmpty("A2") Then Range("E2") = 0 Range("F2") = 0 GoTo a: End If If IsEmpty("A3") Then Range("E2") = 1 Range("F2") = 1 GoTo a: End If Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select nra = Selection.Rows.Count Range("e2") = nra Range("B2").Select Range("A2").Select Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="<" & except, Operator:=xlAnd Range("B2").Select Range(Selection, Selection.End(xlDown)).Select nrb = Selection.Rows.Count Selection.AutoFilter Range("f2") = nrb a: Range("A2").Select Workbooks(objFile.Name).Save Workbooks(objFile.Name).Close True 'saves changes End If Next End Sub "Tom Ogilvy" wrote: if len(a1) = 0 then doesn't test the len of cell A1 - are you just writing pseudo code or . . . Both of the results you reported are consistent with an empty cell. Perhaps you have a logic error in your code. -- Regards, Tom Ogilvy "Joel" wrote: Gary: Len returns 0 Code is #value errror if I change code if len(a1) = 0 then...still doesn't work!!! What else?? Joel "Gary''s Student" wrote: Mystery cells are always fun. Say the cell is A1 What does LEN(A!) show ? What does CODE(A1) show? Does nothing appear in both the cell and formula bar? -- Gary's Student "Joel" wrote: TIA: I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Empty, Null, "", 0
If IsEmpty("A2") Then
Range("E2") = 0 Range("F2") = 0 GoTo a: End If If IsEmpty("A3") Then Range("E2") = 1 Range("F2") = 1 GoTo a: End If should be If IsEmpty(Range("A2")) Then Range("E2") = 0 Range("F2") = 0 GoTo a: End If If IsEmpty(Range("A3")) Then Range("E2") = 1 Range("F2") = 1 GoTo a: End If After spotting those, I didn't check the rest of your code - so you might want to make sure there are no more like that. -- Regards, Tom Ogilvy "Joel" wrote: Here's the code: Sub OpnFiles() Dim objFSO As Object Dim objFolder As Object Dim objFile As Object except = InputBox("Enter Exceptions Value") Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.GetFolder("c:\Copper") 'change directory For Each objFile In objFolder.Files If objFile.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name 'do whatever ' Worksheets("qrycndataall").Activate Range("A2").Select If IsEmpty("A2") Then Range("E2") = 0 Range("F2") = 0 GoTo a: End If If IsEmpty("A3") Then Range("E2") = 1 Range("F2") = 1 GoTo a: End If Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select nra = Selection.Rows.Count Range("e2") = nra Range("B2").Select Range("A2").Select Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:="<" & except, Operator:=xlAnd Range("B2").Select Range(Selection, Selection.End(xlDown)).Select nrb = Selection.Rows.Count Selection.AutoFilter Range("f2") = nrb a: Range("A2").Select Workbooks(objFile.Name).Save Workbooks(objFile.Name).Close True 'saves changes End If Next End Sub "Tom Ogilvy" wrote: if len(a1) = 0 then doesn't test the len of cell A1 - are you just writing pseudo code or . . . Both of the results you reported are consistent with an empty cell. Perhaps you have a logic error in your code. -- Regards, Tom Ogilvy "Joel" wrote: Gary: Len returns 0 Code is #value errror if I change code if len(a1) = 0 then...still doesn't work!!! What else?? Joel "Gary''s Student" wrote: Mystery cells are always fun. Say the cell is A1 What does LEN(A!) show ? What does CODE(A1) show? Does nothing appear in both the cell and formula bar? -- Gary's Student "Joel" wrote: TIA: I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Empty, Null, "", 0
It could contain a space(s) or a non-breaking space (chr(65)).
perhaps something like: s = trim(cell.Value) if len(s) = 0 or s like chr(65) then or s = Trim(cell.Value) s = Replace(s,chr(65),"") if len(s) = 0 then -- Regards, Tom Ogilvy "Joel" wrote: TIA: I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Test for Empty, Null, "", 0
Sorry, chr(65) should have been chr(160) in all cases.
chr(65) is "A" of course and was a mental glitch. -- Regards, Tom Ogilvy "Joel" wrote: TIA: I am testing for contents of a cell (the data was imported from Access to excel). The worksheet cell looks blank (no data) but when I test IsEmpty in False and IsNull is False and cell="" is False I see nothing but every test tells me something is there. If if test not Is Empty is True. If I test cell = 0 if get mismatch of data type (the field is Date format) HELP...Thanks, Joel |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| cells formatted to tick when text value "Y" if or null if "N" | Excel Discussion (Misc queries) | |||
| "IF"- "THEN" type Formula based on Null value | Excel Worksheet Functions | |||
| empty vs. "" vs. Null vs. Nothing vs. not Used vs. Argggh | Excel Programming | |||
| Looking for VB code to test for "RING" , "BUSY" disconnects or other signals | Excel Programming | |||
Hybrid Mode
