Getting macro to record keystrokes instead of cell contents

[dbe4876]

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

Please Login or Register  to view this content.

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?

[AvP11]

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

[dbe4876]

Thanks much. That got us headed in the right direction. One of my compradres in the office tweaked the code a little...

Please Login or Register  to view this content.

Feel free to use it as needed.