Macro to record keystrokes

QUESTION:

1. Is there any way to get Excel to execute the keystrokes EXACTLY as I type them?
Any function, patch, add-in or module?

OR

2. How do you select a Range of cells to execute functions on (Sum, Count, etc.) that is similar/equivalent of {CTRL}+{SHIFT}+{DOWN} so that the Macro still works when the number of ROWS in the data changes?


-----------------------------------------------------------------------------------------------------------
SITUATION:

I have a data file that is sent to us monthly, the data is in exactly the same format every time, but the NUMBER OF ROWS changes.

We do a lot of prep of the data (add/delete columns, sort/format the data, insert formulas =count() =sum(), etc.) and finally generate a “.txt” file that gets uploaded into our accounting software as a journal entry.

Doing this with a “keystroke macro” would save hundreds of hours of work a year.

At home, I have an old computer with Excel 2000, I use Macro Recorder and record all the keystrokes to prep the data and … IT WORKS!

At work, I have a new computer with Excel 2010, I use Macro Recorder and record all the keystrokes and … it works the first month and next month “BOOM goes the dynamite”.

Open the Macro and see the problem is that Visual Basic DOES NOT record the keystrokes EXACTLY as I type them, it records the results … HUGE difference.

So I sum all of the rows in Column A by typing the following keystrokes:

=SUM(
{down}
:
{CTRL}{SHIFT}{DOWN}
)
{ENTER}
{UP}

And Excel version 2010 converts that into:

ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[4628]C)"
Range("C1").Select

It is the (R[2]C:R[4628]C) references that are screwing me up, because my NUMBER OF ROWS changes every month.

-----------------------------------------------------------------------------------------------------------

So far I have tried:

“Use Relative References” button - solves 30% of my problems, but it still uses Offsets() and still uses (C2:C4628) type references.

“ SendKeys” - it almost works, but known timing bugs prevent SendKeys from solving my problem:

SendKeys ("=sum{(}{DOWN}{DOWN}:+^{DOWN}") ‘ works up to this point
SendKeys ("=sum{(}{DOWN}{DOWN}:+^{DOWN}{)}{ENTER}") ‘ does not work once I add ) … returns =sum()

Hello RM-Vallejo,

While it is possible to record the keystrokes and send them, I think you would benefit from design changes to this model. SendKeys is not a reliable method of invoking functions and should not be the backbone of your project. However, I do understand why you have used this method, especially if you are the primary person maintaining this.

Using VBA macros instead of just SendKeys will create a more versatile and robust program. The downside would be an increased learning curve for you with regard to the code. Would this be an option you would consider?