Excel sorting-issue with variable?

Get help with using AutoHotkey and its commands and hotkeys
User avatar
Joe Glines
Posts: 698
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Excel sorting-issue with variable?

10 Jun 2014, 09:32

I have a script which first identifies the column of interest and then sorts on it. When I put in the column to sort on as a number (in below case it is 6) my sort works fine. When I manually define my variable to =6 (Sort_Col_NB:=6), it works fine.
When I use a reference from the following function, it does not work (even though when I check the variable value it shows 6 and when I look at the variable type, it is an integer with no decimal places)
XL_String_To_Number("F")
XL_String_To_Number(Column){
StringUpper, Column, Column
Index := 0
Loop, Parse, Column ;loop for each character
{ascii := asc(A_LoopField)
if (ascii >= 65 && ascii <= 90)
index := index * 26 + ascii - 65 + 1 ;Base = 26 (26 letters)
else { return
} }
return, index
}

Code: Select all

xls.Range("A2:" . LastCol_Alpha . LastRow).Sort(xls.Columns(6),1) ;this works
xls.Range("A2:" . LastCol_Alpha . LastRow).Sort(xls.Columns(Sort_Col_NB),1) ;this does not
The initial error I get in Excel is saying "Specifically: Columns". If I continue running the script it then says there is an issue with "Sort"

Anybody have any ideas on what I'm doing wrong?

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation
toralf
Posts: 792
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: Excel sorting-issue with variable?

10 Jun 2014, 23:19

Just a guess but have tried to add % signs around the var?
Since you didn't provided the full ahk code for the Com or Vba commands it might be that the var name gets passed instead of the value. Or maybe ahk doesn't evaluate the var correctly because of the object.style.code.

Just brain dumps
ciao
toralf
Hamlet
Posts: 32
Joined: 02 Oct 2013, 09:55
Location: Seoul, Korea

Re: Excel sorting-issue with variable?

11 Jun 2014, 00:06

[EDIT]
It feels so uneasy.
I deleted all the comment I wrote.


I do not know the reason clearly, but I could say like this;
The questioner answered selectively.
(S)He considered my answer as a garbage.
At the same time, I felt, was dumped into the garbage can.

I will nerver participate in this kind of dirty game. Promise. For ever.
It sucks.
Last edited by Hamlet on 11 Jun 2014, 12:09, edited 5 times in total.
User avatar
flyingDman
Posts: 860
Joined: 29 Sep 2013, 19:01

Re: Excel sorting-issue with variable?

11 Jun 2014, 01:24

Change the last line if your function to:

Code: Select all

return % index * 1
or, better, dump the function and use:

Code: Select all

col := xl.range("f:f").column
User avatar
Joe Glines
Posts: 698
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: Excel sorting-issue with variable?

11 Jun 2014, 10:55

I didn't post the rest of my script because it is really long and I didn't want to make anyone "dig" for finding my issue. I've used the variable references without wrapping it with % signs in the rest of the script with no issues.

flyingDman- per your suggestion I changed it to:

Code: Select all

xls.Range("A2:" . LastCol_Alpha . LastRow).Sort(xls.Columns(Source_Col . ":"Source_Col . ),1) ;sort sheet by interest
and it works great! Thank you for the help!

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

Return to “Ask For Help”

Who is online

Users browsing this forum: Frosti, Google [Bot], seba1685, TAC109 and 29 guests