MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Todd on March 20, 2001 10:41 AM

I am trying create a AutoSum using Application.SendKey. Below is what I've done. However, when it gets to the SendKey line it just enters a space on my Module. Why is the enter keystroke registering on the Module?
Any Suggestions?
Do Until ActiveCell = "Stop"
For Each cell In Selection
If cell.Value = "Totals" Then
ActiveCell.Offset(0, 1).Range("a1").Select
Application.SendKeys ("%=~")

Posted by Dave Hawley on March 20, 2001 2:24 PM

Hi Todd

Try this way it will be much quicker and easier to read:

Sub TryThis()
Dim MyRange As String
MyRange = Selection.Address
Selection.Find(What:="Totals").Offset(1, 0) = "=Sum(" & MyRange & ")"
End Sub

OzGrid Business Applications

Posted by Bob on March 20, 2001 2:27 PM

Todd, I am kind of new to the VB world, but I use something similar:

Dim LastRow As Variant
LastRow = Range("A65536").End(xlUp).Row

Range("I" & LastRow + 1).Formula = "=Sum(I2:I" & LastRow & ")"

Posted by Ivan Moala on March 20, 2001 5:03 PM

The suggestions by Bob & Dave are good.
Your problem however is due to the
application.sendkeys......this comand will
send the keys to your active screen which
I'm picking is the VBA editor screen.....
when using this command don't run it from within
your open module.


Posted by Todd Melvin on March 21, 2001 8:19 AM

Thanks for the code. However, when I tried to run it I received an error message "Object variable or with block variable not set" I do not know how to correct this error. I just started writing in VB and at this point I can say I like it however I do not fully understand it. So I hope I don't sound stupid.

Posted by Todd Melvin on March 21, 2001 9:46 AM

Thanks for the suggestion. That was my first problem. Now what is happening, is the autoSum is not summing the cell next to the word "Totals". It's running through the column next to the word "Totals" however it only performs an auto sum on the last record prior to the word "stop"
Here is another copy of my code. Any suggestions as to why it won't autosum in every cell next to the word "Totals"? I'm willing to try anything at this point.
Do Until ActiveCell = "Stop"
For Each cell In Selection
If cell.Value = "Totals" Then
ActiveCell.Offset(1, 0).Range("a1").Select
Application.SendKeys "%(=)~", True

ActiveCell.Offset(1, 0).Range("a1").Select
End If
Next cell
End sub

Posted by Dave Hawley on March 21, 2001 5:44 PM

Hi Todd

You don't sound stupid at all! Stupid would be to not ask.

The error is most likely occuring because the "Find" is unable to the the word "Totals" in your selected range.


OzGrid Business Applications