In a Excel sheet, after entering a number into a cell I want to take that number and subtract .05 so for example 50 would become 49.95 . After looking at other post I am finding "Var1 -= .05" but I can't figure out how to assign the number I enter to "Var1" .
Subtracting numbers
Can you explain what process you would like to do?
It sounds like you want to type a number and have it automatically replaced by AHK at a value 0.05 less than what you typed?
That is indeed tricky figuring out how you want to capture this. You might be able to use ControlGet, or using COM with Excel. Otherwise monitoring Input for a number might be viable. Related to the input, you might consider making hotkeys ~0::
through ~9::
and appending their values to a variable, and then do your math and replacement when you press ~Space::
or ~Enter::
Thanks for responding. Basically I would like to enter the number into this Excel cell, then after hitting the hotkey the number in that same cell would be re-entered .05 less. So, if I enter 50 into a cell and run the code that 50 becomes 49.95. Thanks
I would say, try this:
SetTitleMatchMode, 2 #IfWinActive, Excel ~0:: ~1:: ~2:: ; through to ~9:: If A_PriorKey is digit number.=SubStr(A_ThisHotkey,2) else number:=SubStr(A_ThisHotkey,2) return $Enter:: If A_PriorKey is not digit { Send {Enter} return } length:=StrLen(number) number-=0.05 Send {Backspace %length%}%number%{Enter} return
Untested code at the time of posting. Will try to testing when I catch a break in lecture.
SetTitleMatchMode, #IfWinActive Modifiers, Operators (:= -= and .=) If var is type, StrLen()
Edit: That seems to work.
I have noticed that if you wanted decimal numbers, we'd have to rework the script just a little bit with this. The following code is untested.
~0:: ; through to ~9:: ~.:: ; or ~, depending on region If A_PriorKey is digit number.=SubStr(A_ThisHotkey,2) else if (A_PriorKey=".") number.=SubStr(A_ThisHotkey,2) else number:=SubStr(A_ThisHotkey,2) return
Also, there may be trailing zeroes you don't want. If you put just the number into the cell, Excel autotrims the trailing zeroes off for you. But just in case you use numbers you want processed by this AHK script that are not in number-only cells, and if you want decimals with the approach above, I would recommend including the idea:
$Enter:: If A_PriorKey is not digit { Send {Enter} return } length:=StrLen(number) number-=0.05 While SubStr(number,0)=0 StringTrimRight, number, number, 1 Send {Backspace %length%}%number%{Enter} return
While, SubStr(), StringTrimRight,
Though like I said above this code is untested so it may not work out that well. So apologies if it does not work.
I appreciate all the effort here. I guess this is more complicated then I first thought. I am not able to follow what you are doing here. Here is what I have.
~0::
; through to ~9::
~.:: ; or ~, depending on region
If A_PriorKey is digit
number.=SubStr(A_ThisHotkey,2)
else if (A_PriorKey=".")
number.=SubStr(A_ThisHotkey,2)
else
number:=SubStr(A_ThisHotkey,2)
return
=::
If A_PriorKey is not digit
{
Send {Enter}
return
}
length:=StrLen(number)
number-=0.05
While SubStr(number,0)=0
StringTrimRight, number, number, 1
Send {Backspace %length%}%number%{Enter}
return
Is $Enter the hotkey? I did not know how to use it so I changed it to =:: and that may be a problem. If I put 50 into a cell and run I get 5-0.05 and if I enter 51 I get 51-.05. Also when I run in the next cell I get -.1 then -0.15 then -0.2 so it seems to subtract .05 each time unless I reload the script. It seems you have written something in so that 5,55 and 555 would all work correctly but if I enter 555 and change it to 55 without reloading I get something different yet unless I reload.
I don’t know if it matters but this is all these figures are dollar figures so trailing zeros would be a problem. Thanks
Hmm, I hadn't considered backspacing.
Here's the full code that I ran and it worked for me:
(Edit: I forgot the SetTitleMatchMode, 2 line when I first posted.)
SetTitleMatchMode, 2 #IfWinActive, Excel ~0:: ~1:: ~2:: ~3:: ~4:: ~5:: ~6:: ~7:: ~8:: ~9:: ; through to ~9:: If A_PriorKey is digit number.=SubStr(A_ThisHotkey,2) ; A_ThisHotkey will be something like ~1 or ~2. Specify 2 for the second parameter of the SubStr() function means get the 2nd character through to the last character - in a string "~1" or "~2" that is only two characters long, it retrieves just the last character. else number:=SubStr(A_ThisHotkey,2) return $Enter:: If A_PriorKey is not digit ; If you didn't press a number key just before pressing Enter, we assume we want Enter to behave like normal { Send {Enter} return } length:=StrLen(number) ; get the length of the variable number which was created when you pressed number keys number-=0.05 ; subtract from the variable number the 0.05 Send {Backspace %length%}%number%{Enter} ; Replace the number you typed with the new updated value return
Try it without changing anything and see how it behaves. Then we can personalize it to what you want. Do note that the above script still has the problem of trailing zeros.
I will be back in a couple of hours and can work on this script a bit more.
Edited by Exaskryz, 27 October 2015 - 03:16 PM.
Sorry but I’m pretty new to this. Is $Enter the hot key and if so what is the key input for it.
$Enter is indeed the hotkey that should trigger the subtraction.
Edit: I tested this script in full and like how it works. If you want the trigger key to do the subtraction to be = instead of Enter, change $Enter::
into $=::
and Send {Enter}
into Send =
. And depending on your preference, you can remove {Enter} from the line Send {Backspace %length%}%number%{Enter}
SetTitleMatchMode, 2 #IfWinActive, Excel ~0:: ~1:: ~2:: ~3:: ~4:: ~5:: ~6:: ~7:: ~8:: ~9:: ~.:: ; through to ~9:: If A_PriorKey is digit number.=SubStr(A_ThisHotkey,2) else if (A_PriorKey=".") number.=SubStr(A_ThisHotkey,2) else number:=SubStr(A_ThisHotkey,2) return $Enter:: If A_PriorKey is not digit { Send {Enter} return } length:=StrLen(number) number-=0.05 While SubStr(number,0)=0 StringTrimRight, number, number, 1 Send {Backspace %length%}%number%{Enter} return ~Backspace:: If A_PriorKey is digit StringTrimRight, number, number, 1 return
Perfect!! I had changed the hotkey earlier because I thought the $ had something to do with the key sequence and I could not figure it out but after studying the AHK site further I see what it actually is doing. Thanks for all the help here.
One last thing... Now that I started actually using this for data entry I notice this does not work with the keyboard number pad. I tried adding "numpad" before all of the 0-9 numbers at the beginning of the script but that did not work. Also, with the backspace feature you added, usually when I need this it is because I hit two numbers at once when entering data and I have too many digits in the cell and it does not work here. If this is too big of a deal I can just empty the cell and reload the script. Thanks
Edit: I am glad I've tested my suggestions. They don't seem to be working. You can read what I tried below, but I'll come back with a correct answer soon.
This code seems to work.
SetTitleMatchMode, 2 #IfWinActive, Excel ~0:: ~1:: ~2:: ~3:: ~4:: ~5:: ~6:: ~7:: ~8:: ~9:: ~.:: ~NumpadDot:: ~Numpad0:: ~Numpad1:: ~Numpad2:: ~Numpad3:: ~Numpad4:: ~Numpad5:: ~Numpad6:: ~Numpad7:: ~Numpad8:: ~Numpad9:: B_ThisHotkey:=SubStr(A_ThisHotkey,0) If B_ThisHotKey is digit number.=SubStr(A_ThisHotkey,0) else if (A_ThisHotkey="~.") || (A_ThisHotKey="~NumpadDot") number.="." else number:=SubStr(A_ThisHotkey,0) return $NumpadEnter:: $Enter:: B_PriorKey:=SubStr(A_PriorKey,0) If B_PriorKey is not digit { Send {Enter} return } length:=StrLen(number) number-=0.05 While SubStr(number,0)=0 StringTrimRight, number, number, 1 Send {Backspace %length%}%number%{Enter} number:="" return ~Backspace::StringTrimRight, number, number, 1
The spoiler contains bad code.
And amazingly, the code I first gave you should be bad too. The ~Backspace hotkey seems to only work once. A_Priorkey becomes Backspace, so then the If statement is false, and number isn't trimmed anymore. That is, if you typed 999, backspaced twice so on screen you saw 9, the variable number would have a value of 99. Typing out "11" after that, you see 911 on screen, but the script has the value of 9911 on hand. So hitting Enter, your output becomes 9910.95 instead of the expected 910.95.
The number pad is working fine and this script will save me a lot of time. Thanks so much. I don’t know if you ever got a chance to check the backspace feature but with what I have it still is not working. Like I mentioned earlier, I could just empty the cell and reload the script if this is too much trouble. I’m not for sure I put everything together correctly but this is what I am using.
Oh, is the problem with backspace if you type like 22, then hit backspace, then enter, you expect an output of 1.95? Yeah, I see the problem in that.
1) Try using this for the backspace: ~Backspace::StringTrimRight, number, number, 1
2) Here's a revised enter code to accommodate for Backspace presses
$NumpadEnter:: $Enter:: B_PriorKey:=SubStr(A_PriorKey,0) If A_PriorKey!=Backspace ; this is the one line change. If B_PriorKey is not digit { Send {Enter} return } length:=StrLen(number) number-=0.05 While SubStr(number,0)=0 StringTrimRight, number, number, 1 Send {Backspace %length%}%number%{Enter} number:="" return