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

[Jim Thomlinson]

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
--
HTH...

Jim Thomlinson


"inkserious" wrote:

>
> 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
>
>
> --
> inkserious
> ------------------------------------------------------------------------
> inkserious's Profile: http://www.excelforum.com/member.php...o&userid=36734
> View this thread: http://www.excelforum.com/showthread...hreadid=565473
>
>

[inkserious]

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

Confused?

Thanks

-ep