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

On Feb 6, 9:44 am, "Joergen Bondesen" <bonde...@cool.dk> 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" <bonde...@cool.dk> 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 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

Errata....

On Feb 6, 10:14*am, joeu2004 <joeu2...@hotmail.com> 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.

Hi joeu2004

Thanks for your answer.

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

Best regards
Jorgen Bondesen


"joeu2004" <(E-Mail Removed)> skrev i en meddelelse
news:5d5fcb8e-204b-432c-80e3-(E-Mail Removed)...
Errata....

On Feb 6, 10:14 am, joeu2004 <joeu2...@hotmail.com> 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.

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" <(E-Mail Removed)> skrev i en meddelelse
news:(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> skrev i en meddelelse
> news:(E-Mail Removed)...
> > 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

On Feb 6, 12:17*pm, "Joergen Bondesen" <bonde...@cool.dk> 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!

On Feb 6, 12:26*pm, "Joergen Bondesen" <bonde...@cool.dk> 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.

--

Med venlig hilsen
Jørgen Bondesen


"Dave Peterson" <(E-Mail Removed)> skrev i en meddelelse
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> skrev i en meddelelse
>> news:(E-Mail Removed)...
>> > 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