Increment Columns in Excel

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Barn01
Posts: 23
Joined: 28 Jul 2016, 15:53

Increment Columns in Excel

20 Sep 2016, 14:15

I'm using PMC and I don't know what I'm doing wrong of course. I just want to loop through rows and columns. Basically I have 1 or more tables that are various sizes and are shown down sheets in a workbook. I want to save a selected cell in the table to a variable so that I can write it to an input box on a website. I have no problem doing this with going down rows but I can't seem to get the column increment to work in a loop. I've tried .Range but after reading it seems like .Cells is the best way to increment?

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")

        cnt := 1 ;start at Row A
        nodeCnt := 1
        factorCnt := 0
        pctCol := 10 ;start on Col J
        While Xl.Range("B" . %cnt%).Value != "" ;Do until there is an empty cell in B
        {
            label := XL.Range("D" . %cnt%).Value
            pctFactor := 0
            While XL.Cells(%cnt% , %pctCol%).Value != "" ;This returns an array error, but I want to increment the Col until it detects an empty cell
            {
                pctFactor := XL.Cells(%cnt% , 10).Value
                MsgBox, 0, , %label% = %pctFactor% ;label is defined in another part of the script and I have no issue with that
                cnt := %cnt% + 1
                factorCnt := %factorCnt% + 1
            }
            cnt := %cnt% - %factorCnt%
            pctCol := %pctCol% + 1
            factorCnt := %factorCnt% + 1
        }
toralf
Posts: 868
Joined: 27 Apr 2014, 21:08
Location: Germany

Re: Increment Columns in Excel

20 Sep 2016, 14:33

you have put too many % around variables. In an Expression you do not deref the vars.
E.g. cnt := cnt +1
This applies to nearly all yoour code lines. The only line where the % signs are correct is in the MsgBox line (ne Expression)
ciao
toralf
User avatar
Grendahl
Posts: 170
Joined: 30 Sep 2013, 08:21

Re: Increment Columns in Excel

20 Sep 2016, 14:45

As toralf says... no need to deref the variables in expression.

Expression in your case means anything within () and after :=

Your code should look like this. (all i did is remove the extra %'s)

Code: Select all

If !IsObject(XL)
	XL := ComObjCreate("Excel.Application")

cnt := 1 ;start at Row A
nodeCnt := 1
factorCnt := 0
pctCol := 10 ;start on Col J
While Xl.Range("B" . cnt).Value != "" ;Do until there is an empty cell in B
{
	label := XL.Range("D" . cnt).Value
	pctFactor := 0
	While XL.Cells(cnt , pctCol).Value != "" ;This returns an array error, but I want to increment the Col until it detects an empty cell
	{
		pctFactor := XL.Cells(cnt , 10).Value
		MsgBox, 0, , %label% = %pctFactor% ;label is defined in another part of the script and I have no issue with that
		cnt := cnt + 1
		factorCnt := factorCnt + 1
	}
	cnt := cnt - factorCnt
	pctCol := pctCol + 1
	factorCnt := factorCnt + 1
}
Barn01
Posts: 23
Joined: 28 Jul 2016, 15:53

Re: Increment Columns in Excel

20 Sep 2016, 15:03

Thank you both that defiantly makes it much cleaner. I'm new to AHK as you can tell. However I'm still getting an Array error at this line.

Code: Select all

While XL.Cells(cnt , pctCol).Value != ""
Type mismatch
Cells
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Increment Columns in Excel

20 Sep 2016, 15:49

The error is telling you that one or more of the parameters passed to Cells is incorrect.

MsgBox, % cnt ", " pctCol
^^ Add this line into your script on the line before While XL.Cells(cnt , pctCol).Value != "".

Edit: Try this:

Code: Select all

cnt := 1 ;start at Row A
nodeCnt := 1
factorCnt := 0
pctCol := 10 ;start on Col J
try While Xl.Range("B" . cnt).Value != "" ;Do until there is an empty cell in B
{
	label := XL.Range("D" . cnt).Value
	pctFactor := 0
	try While XL.Cells(cnt , pctCol).Value != "" ;This returns an array error, but I want to increment the Col until it detects an empty cell
	{
		pctFactor := XL.Cells(cnt , 10).Value
		MsgBox, 0, , %label% = %pctFactor% ;label is defined in another part of the script and I have no issue with that
		cnt := cnt + 1
		factorCnt := factorCnt + 1
	}
    catch
        throw Exception("Inner while-loop`ncnt=" cnt "`npctCol=" pctCol)
	cnt := cnt - factorCnt
	pctCol := pctCol + 1
	factorCnt := factorCnt + 1
}
catch
    throw Exception("Outer while-loop`ncnt=" cnt "`npctCol=" pctCol)
/Edit
Keep pressing "OK" in the message box until you get the error message. What is the value shown for cnt and pctCol? It is this value that is causing the error.

Spoiler: It's because of this line: cnt := cnt - factorCnt... eventually cnt is a negative number which generates the error. You may need to examine the logic that is calculating cnt.

If you explain more about what you are trying to do, step by step, we may be able to suggest a better option...
Barn01
Posts: 23
Joined: 28 Jul 2016, 15:53

Re: Increment Columns in Excel

20 Sep 2016, 17:09

I've tried a variety of things now and I still get the same error on that while loop. I've put in a msg box above the while statement and cnt = 1 (row), and pctCol = 10 (col J). I get the error msg right after I press ok. I'm not sure why the while loop fails? Possible bug in PMC?

It's a little hard to explain but the whole macro takes an excel sheet that has a single var in col A and labels in col B. If there is a label then there is a formula in col C. There can be 1-4 labels (rows) before a gap. When the end of that set of labels is found paste the correct table (2D table stored in a lookup sheet) beside the label/formula. The table can be anywhere from 10 - 40 cols depending on how many labels are in grouped together. All of the above works BTW .... but then I need to be able to select each value in the table one at a time and send the value to an web input box. I need to be able to scroll via row until the end of the table (1-4 rows) and then start at the top of the next col and repeat until the table has been exhausted. When that table is done I move down Col A until the next var is found and repeat the process. Once that sheet is exhausted I move to the next sheet and do it again.
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Increment Columns in Excel

20 Sep 2016, 18:15

Barn01 wrote:I've tried a variety of things now and I still get the same error on that while loop. I've put in a msg box above the while statement and cnt = 1 (row), and pctCol = 10 (col J). I get the error msg right after I press ok. I'm not sure why the while loop fails? Possible bug in PMC?
I edited my post above when I realized the msgbox wouldn't show the offending value... can you try it again with the modified script I posted above? It should throw an exception showing the values of cnt and pctCol.

You can insert more MsgBoxes into your script. Since the problem is with cnt and pctCol, I suggest putting a MsgBox after each line where you change the value of cnt or pctCol. Check that the values are being calculated as you expect. Something like this maybe:

Code: Select all

MsgBox, % "Starting outer loop. cnt=" cnt
While Xl.Range("B" . cnt).Value != "" ;Do until there is an empty cell in B
{
	label := XL.Range("D" . cnt).Value
	pctFactor := 0
	MsgBox, % "Starting inner loop. cnt=" cnt "`npctCol=" pctCol
	While XL.Cells(cnt , pctCol).Value != "" ;This returns an array error, but I want to increment the Col until it detects an empty cell
	{
		pctFactor := XL.Cells(cnt , 10).Value
		MsgBox, 0, , %label% = %pctFactor% ;label is defined in another part of the script and I have no issue with that
		cnt := cnt + 1
		factorCnt := factorCnt + 1
		MsgBox, % "Inner loop done. Next loop values are.`ncnt=" cnt "`nfactorCnt=" factorCnt "`npctCol=" pctCol
	}
	cnt := cnt - factorCnt
	pctCol := pctCol + 1
	factorCnt := factorCnt + 1
	MsgBox, % "Outer loop done. Next loop values are.`ncnt=" cnt "`nfactorCnt=" factorCnt "`npctCol=" pctCol
}
Barn01
Posts: 23
Joined: 28 Jul 2016, 15:53

Re: Increment Columns in Excel

20 Sep 2016, 20:17

It's pretty wierd. If I change the inner While loop to a For loop until (same expression) then it passes (minus some logic that is screwed up). So why does the While loop fail?

Code: Select all

While XL.Cells(cnt , pctCol).Value != ""
to this

Code: Select all

 Loop
            {
                pctFactor := XL.Cells(cnt , pctCol).Value
                MsgBox, 0, , %label% = %pctFactor%
                cnt := cnt + 1
                factorCnt := factorCnt + 1
                MsgBox, 0, , 
                (LTrim
                Row loop done. Next Col loop values are
                Row cnt = %cnt%
                factorCnt= %factorCnt%
                Col pctCol= %pctCol%
                )
            }
            Until, XL.Cells(cnt, pctCol).Value != ""
kon
Posts: 1756
Joined: 29 Sep 2013, 17:11

Re: Increment Columns in Excel

20 Sep 2016, 21:26

Barn01 wrote:It's pretty wierd. If I change the inner While loop to a For loop until (same expression) then it passes (minus some logic that is screwed up).
Not sure what screwed up logic you are referring to, but that seems like it might be a good place to start looking for the error. :thumbup: The problem is: the values of cnt and pctCol get messed-up at some point. We just need to find out where they are receiving the incorrect values from.
Barn01 wrote:So why does the While loop fail?
The main difference between a While loop and a Loop Until loop is: when is the expression is evaluated. They both evaluate the expression once every iteration of the loop. The while loop evaluates the expression before the body of the loop is executed. The loop until version evaluates the expression after the body of the loop has been executed.
Barn01 wrote:Type mismatch
Cells
This error means that the values given to Cells were not what it was expecting.
Application.Cells(RowIndex, ColumnIndex)
This page lists the values you are allowed to give to Cells: Range.Item
If RowIndex or ColumnIndex are the wrong type of value, Cells will throw the Type Mismatch error.
An example of an illegal value is any negative number. For example, this will throw the same error you are getting: Cells("x", -2)

Did you try the version of the script I posted above that uses try/catch/throw? The error MsgBox it generates should tell you exactly what the values of cnt and pctCol are when the error occurs.
You can also just open the script to view the contents of the variables: When you get the error -> right-click on the script's tray icon -> Open -> in the menu select "View" -> "Variables and their contents"
It should show that the values of cnt and pctCol are wrong. (Not allowed values.)

This might help you debug: XL.Cells(cnt , pctCol).Select
You can use that at any time in your script to select the cell. Usually you don't want to change the Selection if you don't have to, but in this case it could help show you which cell your script is failing at. You'll need to use a MsgBox or maybe Sleep so that the script pauses long enough to see the selection change.
Barn01
Posts: 23
Joined: 28 Jul 2016, 15:53

Re: Increment Columns in Excel

22 Sep 2016, 11:09

Kon thanks for your help!!

I finally got it working. I abandoned trying to sort it out on PMC and used SciTE4 instead and that variable window while stepping is a life saver!!! I haven't coded in 20+ yrs but after a couple hours of hair pulling it started coming back. I'm just not familiar with AHK nor VBA language or syntax, but we all have to start somewhere.

I think I'll still try to recreate this back in PMC because that's where my other macros are and I'd like to use this segment with others. Also I'm curious if I'll still get an error?

Here's the cleaned up script. (well clean by my standards haha)

Code: Select all

While XL.ActiveSheet.Name != "Done"
{
    sht := XL.ActiveSheet.Name
    cnt := 1
    rowA := 1
    While rowA != 1048576
    {
        nodeName := XL.Range("A" . rowA).Value
        pctCol := 10
        While Xl.Cells(rowA, pctCol).Value != ""
        {
            cnt := rowA
            pctFactor := 0
            While XL.Cells(cnt, pctCol).Value != ""
            {
                label := XL.Range("D" . cnt).Value
                pctFactor := XL.Cells(cnt , pctCol).Value
                ; GOTO Web interaction script and return back
                cnt := cnt + 1
            }
            pctCol := pctCol + 1
        }
        XL.Range("A" . cnt).Select
        Sleep, 333
        Send, {End Down}
        Sleep, 333
        Send, {Down}
        Sleep, 333
        Send, {End Up}
        Sleep, 333
        RowA := XL.ActiveCell.Row
    }
    XL.Range("A1").Select
    XL.ActiveSheet.Next.Activate
}
MsgBox, 0, , Done!
Return
Barn01
Posts: 23
Joined: 28 Jul 2016, 15:53

Re: Increment Columns in Excel

22 Sep 2016, 16:27

So I remade the script in PMC and it fails just like it did before. It seems there's a problem with using XL.Cells(var1, var2).Value in a while loop. If I use numbers in place of the var then it works fine, but of course I can't do that for my script to run. Any ideas?

Can someone test this if they have PMC?

Code: Select all

While XL.Cells(cnt, pctCol).Value != ""

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: inseption86, just me, william_ahk and 164 guests