Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Subtracting numbers


  • Please log in to reply
13 replies to this topic
codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

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" .



Exaskryz
  • Members
  • 3249 posts
  • Last active: Nov 20 2015 05:30 AM
  • Joined: 23 Aug 2012

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::



codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

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



Exaskryz
  • Members
  • 3249 posts
  • Last active: Nov 20 2015 05:30 AM
  • Joined: 23 Aug 2012

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.



codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

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



Exaskryz
  • Members
  • 3249 posts
  • Last active: Nov 20 2015 05:30 AM
  • Joined: 23 Aug 2012

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.


codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

Sorry but I’m pretty new to this. Is $Enter the hot key and if so what is the key input for it. 



Exaskryz
  • Members
  • 3249 posts
  • Last active: Nov 20 2015 05:30 AM
  • Joined: 23 Aug 2012

$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


codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

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.



codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

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



Exaskryz
  • Members
  • 3249 posts
  • Last active: Nov 20 2015 05:30 AM
  • Joined: 23 Aug 2012

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.

 

Spoiler



codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

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. 

 

SetTitleMatchMode, 2
#IfWinActive, Excel
~1::
~2::
~3::
~4::
~5::
~6::
~7::
~8::
~9::
~0::
~Numpad1::
~Numpad2::
~Numpad3::
~Numpad4::
~Numpad5::
~Numpad6::
~Numpad7::
~Numpad8::
~Numpad9::
~Numpad0::
~.::
~NumpadDot::
B_ThisHotkey:=SubStr(A_ThisHotkey,0)
If B_ThisHotKey is digit
number.=SubStr(A_ThisHotkey,0)
else if (A_PriorKey=".") || (A_PriorKey="NumpadDot")
number.=SubStr(A_ThisHotkey,0)
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::
B_PriorKey:=SubStr(A_PriorKey,0)
If B_PriorKey is digit
   StringTrimRight, number, number, 1
return


Exaskryz
  • Members
  • 3249 posts
  • Last active: Nov 20 2015 05:30 AM
  • Joined: 23 Aug 2012

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


codude
  • Members
  • 59 posts
  • Last active: Apr 12 2019 03:07 PM
  • Joined: 30 Dec 2014

Right on!!   Thank You..