0E560 = 0?
 

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub


--

Best regards
Joergen Bondesen

0E560 = 0?
 

On Feb 6, 9:44 am, "Joergen Bondesen" wrote:
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?

I cannot duplicate your problem. That is, your macro seems to work
exactly as I would expect, going to the Stop statement only for the
cells that contain "000", "00" and "0".

A good way to debug simple macros like this is to add Debug.Print
statements. Use ctrl-G to see the Immediate Window, where the
Debug.Print output goes.

For example, I replaced the Stop statement with the statement:

Debug.Print "ZeroTRIMCount"

(You can also put a breakpoint there by putting the cursor on that
line, then pressing alt-F9.)

And I added the following statements, showing the pre-existing code
line above:

For Each cell In Selection
cnt = cnt + 1
Debug.Print "----- "; cnt
[....]
TestCVdig = Trim(cell.Value)
Debug.Print VarType(cell); Chr(34) & TestCVdig & Chr(34)
[....]
TestCV = Trim(cell.Value)
Debug.Print Chr(34) & TestCV & Chr(34)
[....]
Debug.Print Chr(34) & TestCVReplace & Chr(34)
Debug.Print Len(TestCV); Len(TestCVReplace)

Of course, I also added the declaration:

Dim cnt as long


----- original posting -----

On Feb 6, 9:44*am, "Joergen Bondesen" wrote:
Hi NG

*Testdata
*0E560
*0E310
*000
*00
*0
*1

*I have experienced an odd problem.
*In a cell, I have this information: * 0E560
*I'm testing cells for Zero's and the above cell contains Zero. Why?
*Macro belowe is my way to solve the probleme.
*Is there a more elegant way?

Option Explicit
Sub test()

* * Dim cell As Range
* * For Each cell In Selection

* * * '// Why zero??
* * * Dim TestCVdig As Double
* * * TestCVdig = Trim(cell.Value)

* * * '// Only trim zero: 0
* * * Dim TestCV As String
* * * TestCV = Trim(cell.Value)

* * * Dim TestCVReplace As String
* * * TestCVReplace = Replace(TestCV, "0", "")

* * * If (Len(TestCV) < Len(TestCVReplace) And _
* * * * Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
* * * * TestCV = "0" Then

* * * * Stop
* * * * '// Function
* * * * 'ZeroTRIMCount cell
* * * End If
* * Next cell
End Sub

--

Best regards
Joergen Bondesen

0E560 = 0?
 

0E560 looks like a number written in scientific notation to VBA--which is very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

0E560 = 0?
 

Errata....

On Feb 6, 10:14*am, joeu2004 wrote:
I cannot duplicate your problem. *That is, your macro seems to work
exactly as I would expect, going to the Stop statement only for the
cells that contain "000", "00" and "0".

But you knew that. You wrote: "Macro belowe is my way to solve the
probleme". Fortunately, Dave understood your problem better and
provided a good answer.

0E560 = 0?
 

Hi joeu2004

Thanks for your answer.

Sorry, please forgive me for my bad English and explanation.
--

Best regards
Jorgen Bondesen


"joeu2004" skrev i en meddelelse
...
Errata....

On Feb 6, 10:14 am, joeu2004 wrote:
I cannot duplicate your problem. That is, your macro seems to work
exactly as I would expect, going to the Stop statement only for the
cells that contain "000", "00" and "0".

But you knew that. You wrote: "Macro belowe is my way to solve the
probleme". Fortunately, Dave understood your problem better and
provided a good answer.

0E560 = 0?
 

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen


"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to VBA--which is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

0E560 = 0?
 

I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was that a number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to VBA--which is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson

0E560 = 0?
 

On Feb 6, 12:17*pm, "Joergen Bondesen" wrote:
Sorry, please forgive me for my bad English and explanation.

Nothing wrong with your English or your explanation. My fault in
reading. Mea culpa!

0E560 = 0?
 

On Feb 6, 12:26*pm, "Joergen Bondesen" wrote:
Please look in macro below.

Is this the macro that caused you problems in the first place? Or is
this yet another attempt to work around the problem?

It would be helpful if you posted the original macro that caused the
problem. You can also share your work-arounds. But I presume you
would prefer the best fix for your original macro.

0E560 = 0?
 

--

Med venlig hilsen
Jørgen Bondesen


"Dave Peterson" skrev i en meddelelse
...
I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was that a
number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to VBA--which
is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson

0E560 = 0?
 

--

Med venlig hilsen
Jørgen Bondesen


"Joergen Bondesen" skrev i en meddelelse
...


--

Med venlig hilsen
Jørgen Bondesen


"Dave Peterson" skrev i en meddelelse
...
I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was that a
number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to VBA--which
is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson

0E560 = 0?
 

Hi Dave.

Sorry about the 2 empty answers.

I do hope this explanation is satisfactory.

Each day my colleagues and I receive Excel files.



I have made a macro to control these files.



I control each cell for containing only . (dot); , (comma); ;
(semicolon); -(minus) and 0 (Zero)

All these things give problem in the further production.



This is my data in cells (we now only concentrate about 0 Zero)
Cell1: 0 zero; [macro will count cell as 0 Zero = ok]
Cell2: '000 starting with apostrophe, [macro will count cell as 0 Zero =
ok]
Cell3: 000 Cell is txt formatted; [macro will count cell as 0 Zero =
ok]



This part test for 0 (Zero)

Sub Test01()

Dim cell As Range

For Each cell In Selection

'// only trim zero: 0

If Trim(cell.Value) = 0 Then

'// Only for illustrate

MsgBox Trim(cell.Value)

'// Function (Count Quantity and gives me cell reference.)

'ZeroTRIMCount cell

End If

Next cell

End Sub



Everything is OK, until my macro finds a cell containing (Cell4:) 0E560
as 0 Zero (Cell format is scientific)

I expect my macro to read: 0E560 and therefore not count the cell as 0
Zero.



This is a huge problem, because our customer will be very angry if this
information is missing in the further production.



To solve my problem, I change my macro to after inspiration from you:


Sub test02()

Dim cell As Range

For Each cell In Selection



Dim InStrE As Long

InStrE = InStr(1, UCase(cell.Value), "E")



If Trim(cell.Value) = 0 And InStrE = 0 Then

'// Only for illustrate

MsgBox cell.Value

'// Function

'ZeroTRIMCount cell

End If

Next cell

End Sub



I'm using Excel 2007 in a Danish version, but I have tested in a UK version,
with the same result.

You can make a Excelfile with all 4 cells and run Test01 and afterwards
Test02.


--

Best Regards
Joergen Bondesen


"Dave Peterson" skrev i en meddelelse
...
I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was that
a number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to
VBA--which is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson

0E560 = 0?
 

Hi joeu2004

Look at my reply to Dave now, please.

--

Best regards
Joergen Bondesen


"joeu2004" skrev i en meddelelse
...
On Feb 6, 12:26 pm, "Joergen Bondesen" wrote:
Please look in macro below.

Is this the macro that caused you problems in the first place? Or is
this yet another attempt to work around the problem?

It would be helpful if you posted the original macro that caused the
problem. You can also share your work-arounds. But I presume you
would prefer the best fix for your original macro.

0E560 = 0?
 

Try using application.isnumber(cell.value) to test to see if the value is
numeric.

if application.isnumber(cell.value) then
if cell.value = 0 then
'increment your count
end if
end if

VBA will try to treat things that look like numbers as numbers.

ps. if you look at the format for that cell with 0E560 in it, you may see
Scientific. But if you use:

=isnumber(a4)
You'll see that it's false.




Joergen Bondesen wrote:

Hi Dave.

Sorry about the 2 empty answers.

I do hope this explanation is satisfactory.

Each day my colleagues and I receive Excel files.

I have made a macro to control these files.

I control each cell for containing only . (dot); , (comma); ;
(semicolon); -(minus) and 0 (Zero)

All these things give problem in the further production.

This is my data in cells (we now only concentrate about 0 Zero)
Cell1: 0 zero; [macro will count cell as 0 Zero = ok]
Cell2: '000 starting with apostrophe, [macro will count cell as 0 Zero =
ok]
Cell3: 000 Cell is txt formatted; [macro will count cell as 0 Zero =
ok]

This part test for 0 (Zero)

Sub Test01()

Dim cell As Range

For Each cell In Selection

'// only trim zero: 0

If Trim(cell.Value) = 0 Then

'// Only for illustrate

MsgBox Trim(cell.Value)

'// Function (Count Quantity and gives me cell reference.)

'ZeroTRIMCount cell

End If

Next cell

End Sub

Everything is OK, until my macro finds a cell containing (Cell4:) 0E560
as 0 Zero (Cell format is scientific)

I expect my macro to read: 0E560 and therefore not count the cell as 0
Zero.

This is a huge problem, because our customer will be very angry if this
information is missing in the further production.

To solve my problem, I change my macro to after inspiration from you:

Sub test02()

Dim cell As Range

For Each cell In Selection

Dim InStrE As Long

InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then

'// Only for illustrate

MsgBox cell.Value

'// Function

'ZeroTRIMCount cell

End If

Next cell

End Sub

I'm using Excel 2007 in a Danish version, but I have tested in a UK version,
with the same result.

You can make a Excelfile with all 4 cells and run Test01 and afterwards
Test02.

--

Best Regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was that
a number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to
VBA--which is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson





--

Dave Peterson

0E560 = 0?
 

I didn't notice that you wanted to count the text '0 and '000 as zeros.

This is more complete:

Option Explicit
Sub Test01()

Dim cell As Range
Dim lCtr As Long
Dim FoundNonDigit As Boolean
Dim ZeroCtr As Long

ZeroCtr = 0
For Each cell In Selection
If Application.IsNumber(cell.Value) Then
If cell.Value = 0 Then
ZeroCtr = ZeroCtr + 1
End If
Else
If IsNumeric(cell.Text) Then
'check for just digits
For lCtr = 1 To Len(cell.Text)
If IsNumeric(Mid(cell.Text, lCtr, 1)) Then
'ok, keep looking
Else
'not a digit, get out
FoundNonDigit = True
Exit For
End If
Next lCtr
End If
If FoundNonDigit = True Then
'don't add it
Else
If CDbl(cell.Value) = 0 Then
ZeroCtr = ZeroCtr + 1
End If
End If
End If
Next cell

MsgBox ZeroCtr

End Sub


Joergen Bondesen wrote:

Hi Dave.

Sorry about the 2 empty answers.

I do hope this explanation is satisfactory.

Each day my colleagues and I receive Excel files.

I have made a macro to control these files.

I control each cell for containing only . (dot); , (comma); ;
(semicolon); -(minus) and 0 (Zero)

All these things give problem in the further production.

This is my data in cells (we now only concentrate about 0 Zero)
Cell1: 0 zero; [macro will count cell as 0 Zero = ok]
Cell2: '000 starting with apostrophe, [macro will count cell as 0 Zero =
ok]
Cell3: 000 Cell is txt formatted; [macro will count cell as 0 Zero =
ok]

This part test for 0 (Zero)

Sub Test01()

Dim cell As Range

For Each cell In Selection

'// only trim zero: 0

If Trim(cell.Value) = 0 Then

'// Only for illustrate

MsgBox Trim(cell.Value)

'// Function (Count Quantity and gives me cell reference.)

'ZeroTRIMCount cell

End If

Next cell

End Sub

Everything is OK, until my macro finds a cell containing (Cell4:) 0E560
as 0 Zero (Cell format is scientific)

I expect my macro to read: 0E560 and therefore not count the cell as 0
Zero.

This is a huge problem, because our customer will be very angry if this
information is missing in the further production.

To solve my problem, I change my macro to after inspiration from you:

Sub test02()

Dim cell As Range

For Each cell In Selection

Dim InStrE As Long

InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then

'// Only for illustrate

MsgBox cell.Value

'// Function

'ZeroTRIMCount cell

End If

Next cell

End Sub

I'm using Excel 2007 in a Danish version, but I have tested in a UK version,
with the same result.

You can make a Excelfile with all 4 cells and run Test01 and afterwards
Test02.

--

Best Regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was that
a number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to
VBA--which is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero. Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson





--

Dave Peterson

0E560 = 0?
 

On Feb 7, 12:41*pm, "Joergen Bondesen" wrote:
For Each cell In Selection
* * If Trim(cell.Value) = 0 Then
* * * MsgBox Trim(cell.Value)
* End If
Next cell

One way:

For Each cell In Selection
If (Application.IsNumber(cell) And cell = 0) Or _
(Not Application.IsNumber(cell) And _
Trim(cell) < "" And Trim(Replace(cell, "0", "")) = "")
Then
' ZeroTRIMCount cell
Debug.Print "OKAY ";
Else
Debug.Print "skip ";
End If
Debug.Print cell.Address; " "; TypeName(cell.Value); " "; _
Chr(34) & cell & Chr(34)
Next cell

I tried this with the following examples (and results):

skip $A$1 Empty ""
skip $A$2 String ""
skip $A$3 String " "
OKAY $A$4 Double "0"
OKAY $A$5 String "0"
OKAY $A$6 String "000"
skip $A$7 String "0E540"
OKAY $A$8 Double "0"
skip $A$9 String "0E300"

Note: A8 was entered as 0E+300, a real scientific notation number.
But of course, Excel changed that to a simple zero. With Scientific
formatting, it display as 0.00E+00.

0E560 = 0?
 

Hi Dave.

Thanks for your macro, I like it.
But I have a question.
You are using cell.value (as displayed in formula bar, yes?) and cell.text
(as displayed in cell, yes?).

I can not figure out why you are using cell.text below. Is it possible for
you to explain it for me?

If IsNumeric(cell.Text) Then
'check for just digits
For lCtr = 1 To Len(cell.Text)
If IsNumeric(Mid(cell.Text, lCtr, 1)) Then
'ok, keep looking
Else
'not a digit, get out
FoundNonDigit = True
Exit For
End If
Next lCtr
End If


--

Best Regards
Joergen Bondesen


"Dave Peterson" skrev i en meddelelse
...
I didn't notice that you wanted to count the text '0 and '000 as zeros.

This is more complete:

Option Explicit
Sub Test01()

Dim cell As Range
Dim lCtr As Long
Dim FoundNonDigit As Boolean
Dim ZeroCtr As Long

ZeroCtr = 0
For Each cell In Selection
If Application.IsNumber(cell.Value) Then
If cell.Value = 0 Then
ZeroCtr = ZeroCtr + 1
End If
Else
If IsNumeric(cell.Text) Then
'check for just digits
For lCtr = 1 To Len(cell.Text)
If IsNumeric(Mid(cell.Text, lCtr, 1)) Then
'ok, keep looking
Else
'not a digit, get out
FoundNonDigit = True
Exit For
End If
Next lCtr
End If
If FoundNonDigit = True Then
'don't add it
Else
If CDbl(cell.Value) = 0 Then
ZeroCtr = ZeroCtr + 1
End If
End If
End If
Next cell

MsgBox ZeroCtr

End Sub


Joergen Bondesen wrote:

Hi Dave.

Sorry about the 2 empty answers.

I do hope this explanation is satisfactory.

Each day my colleagues and I receive Excel files.

I have made a macro to control these files.

I control each cell for containing only . (dot); , (comma); ;
(semicolon); -(minus) and 0 (Zero)

All these things give problem in the further production.

This is my data in cells (we now only concentrate about 0 Zero)
Cell1: 0 zero; [macro will count cell as 0 Zero = ok]
Cell2: '000 starting with apostrophe, [macro will count cell as 0 Zero
=
ok]
Cell3: 000 Cell is txt formatted; [macro will count cell as 0 Zero =
ok]

This part test for 0 (Zero)

Sub Test01()

Dim cell As Range

For Each cell In Selection

'// only trim zero: 0

If Trim(cell.Value) = 0 Then

'// Only for illustrate

MsgBox Trim(cell.Value)

'// Function (Count Quantity and gives me cell reference.)

'ZeroTRIMCount cell

End If

Next cell

End Sub

Everything is OK, until my macro finds a cell containing (Cell4:)
0E560
as 0 Zero (Cell format is scientific)

I expect my macro to read: 0E560 and therefore not count the cell as 0
Zero.

This is a huge problem, because our customer will be very angry if this
information is missing in the further production.

To solve my problem, I change my macro to after inspiration from you:

Sub test02()

Dim cell As Range

For Each cell In Selection

Dim InStrE As Long

InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then

'// Only for illustrate

MsgBox cell.Value

'// Function

'ZeroTRIMCount cell

End If

Next cell

End Sub

I'm using Excel 2007 in a Danish version, but I have tested in a UK
version,
with the same result.

You can make a Excelfile with all 4 cells and run Test01 and afterwards
Test02.

--

Best Regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was
that
a number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to
VBA--which is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero.
Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson





--

Dave Peterson

0E560 = 0?
 

Hi joeu2004.

Thanks for your macro, I like it.

Additionally I am happy for your debugging, where I now can see
possibilities.

--

Best regards
Joergen Bondesen


"joeu2004" skrev i en meddelelse
...
On Feb 7, 12:41 pm, "Joergen Bondesen" wrote:
For Each cell In Selection
If Trim(cell.Value) = 0 Then
MsgBox Trim(cell.Value)
End If
Next cell

One way:

For Each cell In Selection
If (Application.IsNumber(cell) And cell = 0) Or _
(Not Application.IsNumber(cell) And _
Trim(cell) < "" And Trim(Replace(cell, "0", "")) = "")
Then
' ZeroTRIMCount cell
Debug.Print "OKAY ";
Else
Debug.Print "skip ";
End If
Debug.Print cell.Address; " "; TypeName(cell.Value); " "; _
Chr(34) & cell & Chr(34)
Next cell

I tried this with the following examples (and results):

skip $A$1 Empty ""
skip $A$2 String ""
skip $A$3 String " "
OKAY $A$4 Double "0"
OKAY $A$5 String "0"
OKAY $A$6 String "000"
skip $A$7 String "0E540"
OKAY $A$8 Double "0"
skip $A$9 String "0E300"

Note: A8 was entered as 0E+300, a real scientific notation number.
But of course, Excel changed that to a simple zero. With Scientific
formatting, it display as 0.00E+00.

0E560 = 0?
 

The .value of 0E560 will be 0. So using .value won't help me distinguish
between a real 0 and the text 0E560.





Joergen Bondesen wrote:

Hi Dave.

Thanks for your macro, I like it.
But I have a question.
You are using cell.value (as displayed in formula bar, yes?) and cell.text
(as displayed in cell, yes?).

I can not figure out why you are using cell.text below. Is it possible for
you to explain it for me?

If IsNumeric(cell.Text) Then
'check for just digits
For lCtr = 1 To Len(cell.Text)
If IsNumeric(Mid(cell.Text, lCtr, 1)) Then
'ok, keep looking
Else
'not a digit, get out
FoundNonDigit = True
Exit For
End If
Next lCtr
End If

--

Best Regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
I didn't notice that you wanted to count the text '0 and '000 as zeros.

This is more complete:

Option Explicit
Sub Test01()

Dim cell As Range
Dim lCtr As Long
Dim FoundNonDigit As Boolean
Dim ZeroCtr As Long

ZeroCtr = 0
For Each cell In Selection
If Application.IsNumber(cell.Value) Then
If cell.Value = 0 Then
ZeroCtr = ZeroCtr + 1
End If
Else
If IsNumeric(cell.Text) Then
'check for just digits
For lCtr = 1 To Len(cell.Text)
If IsNumeric(Mid(cell.Text, lCtr, 1)) Then
'ok, keep looking
Else
'not a digit, get out
FoundNonDigit = True
Exit For
End If
Next lCtr
End If
If FoundNonDigit = True Then
'don't add it
Else
If CDbl(cell.Value) = 0 Then
ZeroCtr = ZeroCtr + 1
End If
End If
End If
Next cell

MsgBox ZeroCtr

End Sub


Joergen Bondesen wrote:

Hi Dave.

Sorry about the 2 empty answers.

I do hope this explanation is satisfactory.

Each day my colleagues and I receive Excel files.

I have made a macro to control these files.

I control each cell for containing only . (dot); , (comma); ;
(semicolon); -(minus) and 0 (Zero)

All these things give problem in the further production.

This is my data in cells (we now only concentrate about 0 Zero)
Cell1: 0 zero; [macro will count cell as 0 Zero = ok]
Cell2: '000 starting with apostrophe, [macro will count cell as 0 Zero
=
ok]
Cell3: 000 Cell is txt formatted; [macro will count cell as 0 Zero =
ok]

This part test for 0 (Zero)

Sub Test01()

Dim cell As Range

For Each cell In Selection

'// only trim zero: 0

If Trim(cell.Value) = 0 Then

'// Only for illustrate

MsgBox Trim(cell.Value)

'// Function (Count Quantity and gives me cell reference.)

'ZeroTRIMCount cell

End If

Next cell

End Sub

Everything is OK, until my macro finds a cell containing (Cell4:)
0E560
as 0 Zero (Cell format is scientific)

I expect my macro to read: 0E560 and therefore not count the cell as 0
Zero.

This is a huge problem, because our customer will be very angry if this
information is missing in the further production.

To solve my problem, I change my macro to after inspiration from you:

Sub test02()

Dim cell As Range

For Each cell In Selection

Dim InStrE As Long

InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then

'// Only for illustrate

MsgBox cell.Value

'// Function

'ZeroTRIMCount cell

End If

Next cell

End Sub

I'm using Excel 2007 in a Danish version, but I have tested in a UK
version,
with the same result.

You can make a Excelfile with all 4 cells and run Test01 and afterwards
Test02.

--

Best Regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
I don't understand the question.

And you didn't say what was in the cell that displayed 0E560. Was
that
a number
formatted like this? Or was it text?

And what would you expect returned from those 4 cells?

And what was returned from those 4 cells?

Joergen Bondesen wrote:

Hi Dave

Thanks for your answer and inspiration.

0*(10^560) = 0
Yes. Please look in macro below.

This is my data
0 zero
'000 starting with apostrophe
000 cell is txt formated
0E560

Option Explicit

Sub test02()
Dim cell As Range
For Each cell In Selection

Dim InStrE As Long
InStrE = InStr(1, UCase(cell.Value), "E")

If Trim(cell.Value) = 0 And InStrE = 0 Then
MsgBox cell.Value
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub
--

Best regards
Joergen Bondesen

"Dave Peterson" skrev i en meddelelse
...
0E560 looks like a number written in scientific notation to
VBA--which is
very
forgiving.

0*(10^560) = 0

Excel's =isnumber() is much more strict.

Maybe you could use that in your test:

if application.isnumber(cell.value) then
'it's a number...

The value in the cell is really text, right?

Joergen Bondesen wrote:

Hi NG

Testdata
0E560
0E310
000
00
0
1

I have experienced an odd problem.
In a cell, I have this information: 0E560
I'm testing cells for Zero's and the above cell contains Zero.
Why?
Macro belowe is my way to solve the probleme.
Is there a more elegant way?

Option Explicit
Sub test()

Dim cell As Range
For Each cell In Selection

'// Why zero??
Dim TestCVdig As Double
TestCVdig = Trim(cell.Value)

'// Only trim zero: 0
Dim TestCV As String
TestCV = Trim(cell.Value)

Dim TestCVReplace As String
TestCVReplace = Replace(TestCV, "0", "")

If (Len(TestCV) < Len(TestCVReplace) And _
Len(TestCV) 1 And Len(TestCVReplace) = 0) Or _
TestCV = "0" Then

Stop
'// Function
'ZeroTRIMCount cell
End If
Next cell
End Sub

--

Best regards
Joergen Bondesen

--

Dave Peterson

--

Dave Peterson





--

Dave Peterson

--

Dave Peterson