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

Get help with using AutoHotkey and its commands and hotkeys
shawleigh17
Posts: 1
Joined: 27 Nov 2017, 15:30

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

27 Nov 2017, 15:36

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
Posts: 15
Joined: 17 Aug 2016, 01:33

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

27 Nov 2017, 15:53

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
Posts: 453
Joined: 18 Nov 2017, 05:50

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

27 Nov 2017, 16:08

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.
User avatar
jeeswg
Posts: 6904
Joined: 19 Dec 2016, 01:58
Location: UK

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

27 Nov 2017, 16:29

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
Last edited by jeeswg on 27 Nov 2017, 16:33, edited 1 time in total.
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
Osprey
Posts: 453
Joined: 18 Nov 2017, 05:50

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

27 Nov 2017, 16:31

My code looks downright juvenile compared to jeeswg's. Curse you, jeeswg!
User avatar
jeeswg
Posts: 6904
Joined: 19 Dec 2016, 01:58
Location: UK

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

27 Nov 2017, 16:51

- 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
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
Osprey
Posts: 453
Joined: 18 Nov 2017, 05:50

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

27 Nov 2017, 17:18

jeeswg wrote:- 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>.

Return to “Ask For Help”

Who is online

Users browsing this forum: Albireo, Bing [Bot], Google [Bot], HiSoKa, JoeWinograd, kochxd, Nightwolf85, roikl289, supersonic23, swagfag, yuyu and 136 guests