Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from AS400
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from AS400
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy from AS400
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 -- inkseriou ----------------------------------------------------------------------- inkserious's Profile: http://www.excelforum.com/member.php...fo&userid=3673 View this thread: http://www.excelforum.com/showthread.php?threadid=56547 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AS400 copy problem | Excel Programming | |||
AS400 transfer via VBA | Excel Programming | |||
Excel and AS400 DB2 database | Excel Programming | |||
DDE to Query AS400 DB | Excel Programming | |||
SQL against AS400 | Excel Programming |