Copy from AS400

[inkserious]

I often need to copy and paste values from an AS400 system. They are
either positive or negative integers. However, when copying and pasting
into an Excel spreadsheet they are copied as strings as opposed to
numeric values. I have a macro that I've been working on, but it
doesn't seem to function properly. Sometime it will work and other
times it won't.

The postive integers would be of the format 3900
and the negative 3900-

Any ideas?

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 Sub

Thanks.

-ep

 

[Guest]

You are close but what are you doing about positive numbers? Your code only
seems to want to handle the negatives... Try this...

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 IsNumeric(cell.Value) Then
cell.Value = Application.Trim(cell.Value)
If Right(cell.Value, 1) = "-" Then
cell.Value = CInt(Left(cell.Value, _
Len(cell.Value) - 1)) * -1
Else
cell.Value = CInt(cell.Value)
End If
cell.NumberFormat = "0;<0>"
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[inkserious]

It's hanling the postive numbers correctly, but it's still not chanin
the negative numbers from a string to a numeric value.

Confused?

Thanks

-e