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