Excel - Copy previous or next visible cell and increase by 20

[shawleigh17]

Hello,

Is there a way that I can copy the previous or next (depending on which hotkey I use) visible cell and then increase the new cell's value by 20? I had a script that was working using a regedit variable, but I was wondering if I could just use excel directly?

thanks!

[hugin]

Some time since I used VBA in Excel. But it seems to me it should be possible to use VBA and circumvent AHK?
As you said, to use Excel directly.

Also, that way you can set up traps for any bugs etc.

Have you tried to record a macro and then edit it in VBA to your liking? I would start with that. This method has helped me a lot before when running cell checkings etc.

Just a thought

[Osprey]

I agree with hugin that a macro in Excel would be the preferred way to do it. If you want to use AHK anyways, though, you can try this:

Code: Select all

^F11::
  Send, {+Tab}{ctrl down}acv{ctrl up}
  clipboard += 20
  Send, {Tab}{ctrl down}v{ctrl up}
return
^F12::
  Send, {Tab}{ctrl down}acv{ctrl up}
  clipboard += 20
  Send, {+Tab}{ctrl down}v{ctrl up}
return

Press Ctrl+F11 to copy the previous cell and Ctrl+F12 to copy the next cell. Note that I don't have Excel installed, so I haven't tested this in that.

[jeeswg]

A RegEdit variable, Microsoft's Registry Editor? Please explain.

Hopefully this script will do what you want:

Code: Select all

;[Excel_Get function]
;MS-Office-COM-Basics/Excel_Get.ahk at master · ahkon/MS-Office-COM-Basics · GitHub
;https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk

q:: ;excel - set active cell value based on cell above/below
w:: ;excel - set active cell value based on cell above/below
oXl := Excel_Get()
vOffset := InStr(A_ThisHotkey, "q") ? -1 : 1
oXl.ActiveCell.Value := 20 + oXl.ActiveCell.Offset(vOffset, 0).Range("A1").Value
oXl := ""
return

e:: ;excel - active cell get info
oXl := Excel_Get()
vText := oXl.ActiveCell.Formula
. "`r`n" oXl.ActiveCell.Text
. "`r`n" oXl.ActiveCell.Value
. "`r`n" oXl.ActiveCell.Value2
MsgBox, % vText
oXl := ""
return

[Osprey]

My code looks downright juvenile compared to jeeswg's. Curse you, jeeswg!

[jeeswg]

- Haha, @Osprey, you've only been on the forum about 10 days, and you're already making an impact, so I don't think that it will take very long for you to learn VBA in AHK if you ever get MS Office again. There are cases where objects make things much easier than sending key presses.
- Here's an example with more traditional methods.
- When you copy to the clipboard, Excel adds a trailing CRLF, which you have to remove before you can do the mathematical calculation.
- Note: when you use VBA from within AutoHotkey, AutoHotkey doesn't know all of the xl constant values, so you need to define them somewhere e.g. xlLastCell := 11.
MS Office Excel COM Interface Constants - AutoHotkey Community
https://autohotkey.com/boards/viewtopic.php?f=7&t=33914

Code: Select all

q:: ;excel - set active cell value based on cell above/below
w:: ;excel - set active cell value based on cell above/below
oDirection := InStr(A_ThisHotkey, "q") ? ["Up","Down"] : ["Down","Up"]
Clipboard := ""
Send, % "{" oDirection.1 "}^c"
ClipWait, 3
if ErrorLevel
{
	oDirection := ""
	return
}
Clipboard := SubStr(RTrim(Clipboard, "`r`n"), 1) + 20
Send, % "{" oDirection.2 "}^v"
return

[Osprey]

- Haha, @Osprey, you've only been on the forum about 10 days, and you're already making an impact, so I don't think that it will take very long for you to learn VBA in AHK if you ever get MS Office again. There are cases where objects make things much easier than sending key presses.

I may have registered only 10 days ago, but I'll have you know that I've been scripting in AHK for a whole 2.5 months already. 2.5 months! I am no noob. I've graduated all of the way up to novice <pats self on back>.