I have a range of cells A1:A10 in which data is pasted from AS400. Th
data is pasted in a 2340- format for a negative number. What I'
looking for is when the data is pasted into these cells, for excel t
automatically change the format from 2340- to <2340.

Thanks.

e

--
edwardpestia
-----------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...fo&userid=3380
View this thread: http://www.excelforum.com/showthread.php?threadid=55636

Hi Edward,

Try:

'=============
Public MoveMinus()
Dim rCell As Range
Dim rng As Range

On Error Resume Next
Set rng = ActiveSheet.Range("A1:A10")
On Error GoTo 0

For Each rCell In rng
With rCell
If IsNumeric(.Value) Then
.Value = CDbl(.Value)
End If
End With
Next rCell

End Sub
'<<=============

---
Regards,
Norman



"edwardpestian"
wrote in message
news:edwardpestian.2a3snz_1151483403.6853@excelfor um-nospam.com...

I have a range of cells A1:A10 in which data is pasted from AS400. The
data is pasted in a 2340- format for a negative number. What I'm
looking for is when the data is pasted into these cells, for excel to
automatically change the format from 2340- to <2340.

Thanks.

ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=556362

On Wed, 28 Jun 2006 03:27:34 -0500, edwardpestian wrote:

I have a range of cells A1:A10 in which data is pasted from AS400. The
data is pasted in a 2340- format for a negative number. What I'm
looking for is when the data is pasted into these cells, for excel to
automatically change the format from 2340- to <2340.

You can try this:

dim c as excel.range
for each c in range("put-your-column-address-here")
if c.value like "*-" then c.value = -val(c.value)
next

and then format negative values as you want.

--
PL

It compiles okay, but does not work when data is typed in any of th
cells in the range.

Thanks.

e

--
edwardpestia
-----------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...fo&userid=3380
View this thread: http://www.excelforum.com/showthread.php?threadid=55636

Hi Edward,

Try:

'=============
Public Sub MoveMinus()
Dim rCell As Range
Dim rng As Range

On Error Resume Next
Set rng = ActiveSheet.Range("A1:A10")
On Error GoTo 0

For Each rCell In rng
With rCell
.NumberFormat = "#,##0.00" '<<==== CHANGE
If IsNumeric(.Value) Then
.Value = CDbl(.Value)
End If
End With
Next rCell

End Sub
'<<=============


---
Regards,
Norman



"edwardpestian"
wrote in message
news:edwardpestian.2a3u23_1151485242.0971@excelfor um-nospam.com...

It compiles okay, but does not work when data is typed in any of the
cells in the range.

Thanks.

ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=556362

Could I be doing something wrong? Says compile error: Invalid Outsid
Procedure.

The following code is highlighted:

On Error Resume Next

e

--
edwardpestia
-----------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...fo&userid=3380
View this thread: http://www.excelforum.com/showthread.php?threadid=55636

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Right(cell.Value, 1) = "-" Then
cell.Value = Left(cell.Value, Len(cell.Value) - 1) * -1
cell.NumberFormat = "0;<0"
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Su

--
edwardpestia
-----------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...fo&userid=3380
View this thread: http://www.excelforum.com/showthread.php?threadid=55636