Number-format...
Lospanga
When I import data from a database to Excel - all my negativ numbers is
converting wrong. All my negative numbert - start with the number and after
the number the "negative-character" comes (ex 230-). This is my problem
because Excel don´t think that my negative numbers are numbers, Excel think
that all my negative numbers are text. I want to write all negative
numbert with the "negative-character" first (ex -230).
Soo, if somebody know how to write something in VB, that searching for all
the "negative-character" ( - ) and put the "negative-character" first in all
the cell, please help me.
One more question.....
Do somebody know who to make one cell "non-printing". I want to see the
information on the screen but I don´t want the information when I print.
Madeleine
Patrick Molloy
Dim rCell As Range
On Error Resume Next
For Each rCell In ActiveSheet.UsedRange
If rCell <> "" Then
If Right(rCell, 1) = "-" Then
rCell = -Val(Left(rCell, Len(rCell) - 1))
End If
End If
Next
End Sub
--
Patrick Molloy
Microsoft Excel MVP
www.xl-expert.com pat...@NOSPAMxl-expert.com
_________________________________
"Lospanga" <losp...@swipnet.se> wrote in message
news:3JYO4.14661$uJ1....@nntpserver.swip.net...
Harald Staff
Try this. You may insert the conversion statement into your existing code if
the import is done by VB.
Sub ConvertNumbers()
Dim TheArea As Range
Dim cel As Object
On Error Resume Next
Set TheArea = Application.InputBox("Select " & _
"the troublesome number area with the mouse:", Type:=8)
For Each cel In TheArea.Cells
If Right(cel.Value, 1) = "-" Then _
cel.Value = CDbl(cel.Value)
Next
End Sub
Best wishes Harald
Lospanga <losp...@swipnet.se> skrev i
Myrna Larson
>When I import data from a database to Excel - all my negativ numbers is
>converting wrong. All my negative numbert - start with the number and after
>the number the "negative-character" comes (ex 230-). This is my problem
>because Excel don´t think that my negative numbers are numbers, Excel think
>that all my negative numbers are text. I want to write all negative
>numbert with the "negative-character" first (ex -230).
>Soo, if somebody know how to write something in VB, that searching for all
>the "negative-character" ( - ) and put the "negative-character" first in all
>the cell, please help me.
Sub FixNegatives()
Dim Cell As Range, V As Variant
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each Cell In ActiveSheet.UsedRange
With Cell
V = .Value
If TypeName(V) = "String" Then
If Right$(V, 1) = "-" Then
Cell.Value = Left$(V, Len(V) - 1) * -1
End If
End If
End With
Next Cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
>Do somebody know who to make one cell "non-printing". I want to see the
>information on the screen but I don´t want the information when I print.
You can, of course, put the cell in an area of the worksheet that isn't part
of the print area.
If that means you can't see it on the screen, either (because of the
location), you can insert a text box that shows it. Let's say you put the
value in cell AB250. Insert a Text Box. Then, instead of typing in the box,
click on the formula bar and type the formula =AB250. Then set the properties
of the text box so that it doesn't print.
Dana DeLouis
rng = Format(rng)
However, Peter Surcouf suggested using CDbl a while ago here in the
newsgroups.
rng = CDbl(rng)
The both do the same thing. (ie changing 123- to -123, and not changing the
text "test-" to 0 (zero) as using the Val() function would do.)
However, CDbl appears to be faster, especially if the text is really text
that should not be converted to a negative number (ie a cell had "-not a
number-"). My guess is that Format() actually does its thing on each cell,
if it is text or a number. It appears that CDbl can more quickly determine
if the text can be converted. If the data is actually text, it more
quickly moves on and does not bother doing the actual CDbl routine. I am
not an expert, so this is only a guess. Maybe someone can add to this idea.
Anyway, I use the following. I use special cells to only look at text. (no
need to waste time looking at numbers.) I also do not want to look at
formulas. If a Cell had the formula =1 / 3, then using CDbl on this cell
would change the cell to 0.33333333 by replacing the formula with the actual
value. This is probably not what is wanted. Anyway, just another idea to
consider. Dana DeLouis
Sub TrailingMinus()
' = = = = = = = = = = = = = = = =
' General idea from Peter Surcouf
' = = = = = = = = = = = = = = = =
Dim rng As Range
Dim BigRng As Range
On Error Resume Next
Set BigRng = Cells.SpecialCells(xlConstants, xlTextValues).Cells
If BigRng Is Nothing Then Exit Sub
For Each rng In BigRng
rng = CDbl(rng)
Next
End Sub
"Lospanga" <losp...@swipnet.se> wrote in message
news:3JYO4.14661$uJ1....@nntpserver.swip.net...
> I have a problem. I will try too write as good in english that I can.
>
> When I import data from a database to Excel - all my negativ numbers is
> converting wrong. All my negative numbert - start with the number and
after
> the number the "negative-character" comes (ex 230-). This is my problem
> because Excel don´t think that my negative numbers are numbers, Excel
think
> that all my negative numbers are text. I want to write all negative
> numbert with the "negative-character" first (ex -230).
> Soo, if somebody know how to write something in VB, that searching for all
> the "negative-character" ( - ) and put the "negative-character" first in
all
> the cell, please help me.
>
>
> One more question.....
>
> Do somebody know who to make one cell "non-printing". I want to see the
> information on the screen but I don´t want the information when I print.
>
>
> Madeleine
Dana DeLouis
different (& better in my opinion) then the Worksheet Value() function .
Therefore, I believe one could shorten the code from:
> rCell = -Val(Left(rCell, Len(rCell) - 1))
> rCell = -Val(rCell)
Both of these return 123.
Debug.Print Val("123 Main St.")
Debug.Print Val("123-")
Be aware that
Val("test-")
will return 0 (zero).
Just a thought. Dana DeLouis.
"Patrick Molloy" <patrick...@xxxhotmail.com> wrote in message
news:OC$ZEgss$GA....@cppssbbsa02.microsoft.com...
> Sub changeNumber()
> Dim rCell As Range
> On Error Resume Next
> For Each rCell In ActiveSheet.UsedRange
> If rCell <> "" Then
> If Right(rCell, 1) = "-" Then
> rCell = -Val(Left(rCell, Len(rCell) - 1))
> End If
> End If
> Next
> End Sub
>
>
> --
> Patrick Molloy
> Microsoft Excel MVP
> www.xl-expert.com pat...@NOSPAMxl-expert.com
> _________________________________
>
>