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" <
[email protected]> 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" <
[email protected]> 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
