Copy from AS400

[inked]

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

[geekgirlau]

Can you post a sample of the raw data?

[inked]

Negative text strings

3,272-
2,400-
2,000-
1,875-
1,300-
1,000-

Positive text strings

6,500
2,500
2,400
1,575
1,400
1,150

Using a formula such as this returns the expected result, but it forces the user to enter the strings in another cell. I would prefer the user simply paste the strings directly into the cell which would require the use of VBA



=IF(ISERROR(IF(ISNUMBER(FIND("-",$D45)),-SUBSTITUTE($D45,"-",""),SUBSTITUTE($D45,"+",""
)+0)),"",IF(ISNUMBER(FIND("-",$D45)),-SUBSTITUTE($D45,"-",""),SUBSTITUTE($D45,"+",""
)+0))

Thanks for the help

-ep

[geekgirlau]

Depending on how much data you are dealing with, it can often be quicker to work with a formula rather than looping through the cells individually.

One method would be using your formula (or something like "=IF(RIGHT(A1,1)="-",VALUE("-"&LEFT(A1,LEN(A1)-1)),VALUE(A1))"), then copying the formula cells and pasting the value over the original data.

Your code could create the formula, copy the value over the original data, then clear the formula cells.

[inked]

It's only about 10 cells. I'd prefer to stay with code as opposed to formulas.

[jindon]

Hi
Have you tried

cell.NumberFormat = "0;<0>"
cell.Value = Val(cell.Value) * -1