I need to paste data into Excel from OMNIStation to do roll-ups on
specific fields. But OMNIStation designates negative numbers with a
minus sign at the end of the number (e.g., "$10,236.60-"). So, I tried
recording a macro to remove the minus sign at the end and put it at the
beginning, at which point Excel then recognizes the number as a negative
value. Problem is, Excel doesn't record the keystrokes, rather it is
recording the contents of the cell...
Code:
--------------------
' Keyboard Shortcut: Ctrl+a
'
ActiveCell.FormulaR1C1 = " $10,236.60"
Range("D13").Select
End Sub
--------------------
That means when I run the macro on the next negative value cell it just
copies the same value into that cell. Not much help. Is there a way to
get Excel to record the actual keystrokes and not treat this as a
formula?


--
dbe4876
------------------------------------------------------------------------
dbe4876's Profile: http://www.excelforum.com/member.php...o&userid=27415
View this thread: http://www.excelforum.com/showthread...hreadid=469293

i think the easiest way to do it is writing a loop checking all data in your
sheet.
Copy this module into your visual basic editor:

Sub ChangeMinusSign()
Dim x As Integer, y As Integer, OldNumber As String, NewNumber As Long
For x = 1 To 500 ' change the 500 to the max of rows
For y = 1 To 50 ' change 50 to the max of columns
If Right(Cells(x, y).Text, 1) = "-" Then '
check if the most right character is the minus sign
OldNumber = "-" & Left(Cells(x, y).Text, Len(Cells(x,
y).Text) - 1) ' writes a minus sign, followed by the number, without the
right standing minus sign
OldNumber = Replace(OldNumber, ",", ".") 'This works, if
decimal separator is comma. If dot (".") remove this line
Cells(x, y).Formula = OldNumber
End If
Next
Next
End Sub


"dbe4876" wrote:


I need to paste data into Excel from OMNIStation to do roll-ups on
specific fields. But OMNIStation designates negative numbers with a
minus sign at the end of the number (e.g., "$10,236.60-"). So, I tried
recording a macro to remove the minus sign at the end and put it at the
beginning, at which point Excel then recognizes the number as a negative
value. Problem is, Excel doesn't record the keystrokes, rather it is
recording the contents of the cell...
Code:
--------------------
' Keyboard Shortcut: Ctrl+a
'
ActiveCell.FormulaR1C1 = " $10,236.60"
Range("D13").Select
End Sub
--------------------
That means when I run the macro on the next negative value cell it just
copies the same value into that cell. Not much help. Is there a way to
get Excel to record the actual keystrokes and not treat this as a
formula?


--
dbe4876
------------------------------------------------------------------------
dbe4876's Profile: http://www.excelforum.com/member.php...o&userid=27415
View this thread: http://www.excelforum.com/showthread...hreadid=469293

Thanks much. That got us headed in the right direction. One of my
compradres in the office tweaked the code a little...
Code:
--------------------
Sub ChangeMinusSign()
iRows$ = InputBox$("Enter number of rows", "Rows", "500")
iCols$ = InputBox$("Enter number of columns", "Columns", "500")

Dim x As Integer, y As Integer, OldNumber As String, NewNumber As Long
x = CStr(iRows$)
y = CStr(iCols$)
For x = 1 To 500 ' change the 500 to the max of rows
For y = 1 To 50 ' change 50 to the max of columns
If Right(Cells(x, y).Text, 1) = "-" Then
OldNumber = "-" & Left(Cells(x, y).Text, Len(Cells(x, y).Text) - 1)
OldNumber = Replace(OldNumber, ",", ".")
Cells(x, y).Formula = OldNumber
End If
Next
Next
End Sub
--------------------
Feel free to use it as needed.


--
dbe4876
------------------------------------------------------------------------
dbe4876's Profile: http://www.excelforum.com/member.php...o&userid=27415
View this thread: http://www.excelforum.com/showthread...hreadid=469293