How to program an excel macro to repeat a series of keystrokes?
 

I want to program an Excel macro to set a columnar range, then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the end of the range
is reached. How do I teach the macro to automatically repeat the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your help!!!

Hi there,

Look up Sendkeys in the visual basic editor help.

Hope that helps

Naz,
London

"Beancounter" wrote:

I want to program an Excel macro to set a columnar range, then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the end of the range
is reached. How do I teach the macro to automatically repeat the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your help!!!

Now I understand about Sendkeys and see how they could be useful in this
situation, but how/where do I record one and integrate it into my macro?

"Naz" wrote:

Hi there,

Look up Sendkeys in the visual basic editor help.

Hope that helps

Naz,
London

"Beancounter" wrote:

I want to program an Excel macro to set a columnar range, then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the end of the range
is reached. How do I teach the macro to automatically repeat the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your help!!!

Bean

Sounds like you are attempting to add an = sign to existing cell contents.

For what purpose? To create a formula? What is the data currently in the
cells?

Perhaps there is another way to do what you want.

I can think of a User Defined Function that evaluates cell contents depending
upon what is currently entered in the cells.

Or a macro that will add the = sign to all cells if that's all you want.


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 13:09:03 -0800, "Beancounter"
wrote:

I want to program an Excel macro to set a columnar range, then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the end of the range
is reached. How do I teach the macro to automatically repeat the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your help!!!

Yes, I need to create a formula from the existing cell contents by simply
adding a = sign to the cell's current contents (over 12,000 cells in one
column). I, too, thought a macro would work, but don't know how to program
it to repeat the keystroke series "F2, Home, =, Enter" in a columnar range.
Many thanks for your assistance.
-Bean

"Gord Dibben" wrote:

Bean

Sounds like you are attempting to add an = sign to existing cell contents.

For what purpose? To create a formula? What is the data currently in the
cells?

Perhaps there is another way to do what you want.

I can think of a User Defined Function that evaluates cell contents depending
upon what is currently entered in the cells.

Or a macro that will add the = sign to all cells if that's all you want.


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 13:09:03 -0800, "Beancounter"
wrote:

I want to program an Excel macro to set a columnar range, then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the end of the range
is reached. How do I teach the macro to automatically repeat the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your help!!!

Don't use SendKeys. Instead, select the cells in question and run
the following macro:

Sub AAA()
Dim Rng As Range
For Each Rng In Selection.Cells
Rng.Formula = "=" & Rng.Text
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Beancounter" wrote in
message
...
Yes, I need to create a formula from the existing cell contents
by simply
adding a = sign to the cell's current contents (over 12,000
cells in one
column). I, too, thought a macro would work, but don't know
how to program
it to repeat the keystroke series "F2, Home, =, Enter" in a
columnar range.
Many thanks for your assistance.
-Bean

"Gord Dibben" wrote:

Bean

Sounds like you are attempting to add an = sign to existing
cell contents.

For what purpose? To create a formula? What is the data
currently in the
cells?

Perhaps there is another way to do what you want.

I can think of a User Defined Function that evaluates cell
contents depending
upon what is currently entered in the cells.

Or a macro that will add the = sign to all cells if that's all
you want.


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 13:09:03 -0800, "Beancounter"
wrote:

I want to program an Excel macro to set a columnar range,
then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the
end of the range
is reached. How do I teach the macro to automatically repeat
the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your
help!!!

Bean

Without seeing a sample of your data it is hard to tell whether or not your
formulas will work when you do get the = sign inserted.

If data is a text string like 2+3+5+(12+20)*10 then a UDF will work.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Copy the above function and paste into a General Module in your workbook.

Assume A1 has the above text string.

In B1 enter =EvalCell(A1) to return 330

Double-click on fill handle of B1 to copy formula down until end of data in
column A.

To add an = sign(or any other text at left)to existing cells use this macro.

Sub Add_Text_Left()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
For Each Cell In thisrng
Cell.Value = moretext & Cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord

On Sat, 22 Jan 2005 15:05:01 -0800, "Beancounter"
wrote:

Yes, I need to create a formula from the existing cell contents by simply
adding a = sign to the cell's current contents (over 12,000 cells in one
column). I, too, thought a macro would work, but don't know how to program
it to repeat the keystroke series "F2, Home, =, Enter" in a columnar range.
Many thanks for your assistance.
-Bean

"Gord Dibben" wrote:

Bean

Sounds like you are attempting to add an = sign to existing cell contents.

For what purpose? To create a formula? What is the data currently in the
cells?

Perhaps there is another way to do what you want.

I can think of a User Defined Function that evaluates cell contents depending
upon what is currently entered in the cells.

Or a macro that will add the = sign to all cells if that's all you want.


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 13:09:03 -0800, "Beancounter"
wrote:

I want to program an Excel macro to set a columnar range, then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the end of the range
is reached. How do I teach the macro to automatically repeat the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your help!!!

A million thanks to you, Chip. This works beautifully, and is precisely what
I needed!
Your fan,
Beancounter

"Chip Pearson" wrote:

Don't use SendKeys. Instead, select the cells in question and run
the following macro:

Sub AAA()
Dim Rng As Range
For Each Rng In Selection.Cells
Rng.Formula = "=" & Rng.Text
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Beancounter" wrote in
message
...
Yes, I need to create a formula from the existing cell contents
by simply
adding a = sign to the cell's current contents (over 12,000
cells in one
column). I, too, thought a macro would work, but don't know
how to program
it to repeat the keystroke series "F2, Home, =, Enter" in a
columnar range.
Many thanks for your assistance.
-Bean

"Gord Dibben" wrote:

Bean

Sounds like you are attempting to add an = sign to existing
cell contents.

For what purpose? To create a formula? What is the data
currently in the
cells?

Perhaps there is another way to do what you want.

I can think of a User Defined Function that evaluates cell
contents depending
upon what is currently entered in the cells.

Or a macro that will add the = sign to all cells if that's all
you want.


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 13:09:03 -0800, "Beancounter"
wrote:

I want to program an Excel macro to set a columnar range,
then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the
end of the range
is reached. How do I teach the macro to automatically repeat
the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your
help!!!

Gord,
Thank you for your assistance. Chip Pearson sent me the macro I needed, and
it worked perfectly. I have never used this support community before, but I
am so impressed with all the great help I got right away. Thanks again!!
Yours,
Bean

"Gord Dibben" wrote:

Bean

Without seeing a sample of your data it is hard to tell whether or not your
formulas will work when you do get the = sign inserted.

If data is a text string like 2+3+5+(12+20)*10 then a UDF will work.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Copy the above function and paste into a General Module in your workbook.

Assume A1 has the above text string.

In B1 enter =EvalCell(A1) to return 330

Double-click on fill handle of B1 to copy formula down until end of data in
column A.

To add an = sign(or any other text at left)to existing cells use this macro.

Sub Add_Text_Left()
Dim Cell As Range
Dim moretext As String
Dim thisrng As Range
On Error GoTo endit
Set thisrng = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
moretext = InputBox("Enter your Text")
For Each Cell In thisrng
Cell.Value = moretext & Cell.Value
Next
Exit Sub
endit:
MsgBox "only formulas in range"
End Sub


Gord

On Sat, 22 Jan 2005 15:05:01 -0800, "Beancounter"
wrote:

Yes, I need to create a formula from the existing cell contents by simply
adding a = sign to the cell's current contents (over 12,000 cells in one
column). I, too, thought a macro would work, but don't know how to program
it to repeat the keystroke series "F2, Home, =, Enter" in a columnar range.
Many thanks for your assistance.
-Bean

"Gord Dibben" wrote:

Bean

Sounds like you are attempting to add an = sign to existing cell contents.

For what purpose? To create a formula? What is the data currently in the
cells?

Perhaps there is another way to do what you want.

I can think of a User Defined Function that evaluates cell contents depending
upon what is currently entered in the cells.

Or a macro that will add the = sign to all cells if that's all you want.


Gord Dibben Excel MVP

On Sat, 22 Jan 2005 13:09:03 -0800, "Beancounter"
wrote:

I want to program an Excel macro to set a columnar range, then perform the
keystroke series "F2, Home, =, Enter" repeatedly until the end of the range
is reached. How do I teach the macro to automatically repeat the keystoke
series? I am using XP, and Excel 2002 10.4. Thanks for your help!!!