AS400 copy problem

[edwardpestian]

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

 

[Norman Jones]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[Piotr Lipski]

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.

 

[edwardpestian]

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

Thanks.

e

 

[Norman Jones]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[edwardpestian]

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

The following code is highlighted:

On Error Resume Next

e

 

[edwardpestian]

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