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

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

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

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

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

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

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

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

Test for Empty, Null, "", 0
 

Tom:

Thanks to you for your time to spot the error...much appreciated.

Joel

"Tom Ogilvy" wrote:

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