Can Anyone Tell Me What's Wrong with this Code I've Written?

[rjw524]

Hi All,

I have what should be a very simple problem to solve. I've frequently had problems with nesting IF/Else or IF/ENDIF commands in MS 13. I've tried for hours with this below and just can't get it to work.

What I'm TRYING to Do:

I have a spreadsheet that has 2 worksheets. The macro copies a name from FIRST and opens a FIND box in LAST.

Now, the name copied from FIRST is always in LAST, that's not the problem. The problem is the next step.

After finding the name in LAST, the macro does a second Find on the LAST worksheet.

On the LAST worksheet I ask the macro to find the word "current" after selecting a block of rows that make up an entire data record. (The info in FIRST has an empty line between each record, so selecting the rows is a simple CTRL+SHIFT+PRESS DOWN command).

FOLLOWING IS THE PROBLEM...

Most records on the LAST sheet have the word "current" in the subsequent rows. A few (say 1 out of 20) do not. So, I wanted to do a simple IF/THEN that would have the Macro go back to the FIRST sheet (which is still on the original name it copied to begin with) move over two cells to the right and type the word "NotFound".

This is where my macro goes KABOOM!!! So, I'm including it here for you guys to show me the error of my ways (as you always do thank goodness!)

*Starting from the Image Recognition that opens a window when the word "Current" is not found.

Let>FIP_SCANPIXELS=ALL
FindImagePos>%SCRIPT_DIR%\ExcelCannotFind.bmp,SCREEN,0,1,XArr,YArr,numFound
If>numFound=1
Gosub>NotFound
Else
Goto Continue
EndIF

SRT>NotFound

CloseWindow>Microsoft Excel

Press CTRL
Press Page Down
Release CTRL

(unnecessary code that works fine)...

Goto StartOver
End>NotFound

Label Continue

//NOTE: MY THINKING HERE IS THAT, IF THE IMAGE IS NOT FOUND (numFound=0) THE WORD "CURRENT" WAS IN THE RECORD AND THE MACRO CAN CONTINUE AS PLANNED.

CloseWindow>Find and Replace
wait 0.2

Press down
wait 0.2

(more unnecessary code that works fine).

Goto StartOver

The problem is that my code won't go to the Subroutine NotFound if the conditions for it to do so are satisfied (i.e. numFound=1).

What am I doing wrong (note...I've tried a number of ways to get this to work. this example above is where I was at when I said to myself "take it to the mjtnet forum".)

Thanks!

[Djek]

hi,
it's a fuzzy story, but it seams to me that you better use the command XLSheetToArray
to read all the data of the excel sheet to determine if theres a cell in it with a specific value.
Isnt that better then to use a image recognition?
kind regards
Djek

[Marcus Tettmar]

I wouldn't use image recognition to automate Excel. There are built in XL functions to do that, and you can augment those with VBScript to directly control and access everything in excel without automating the UI.


Sent from my iPad using Tapatalk

[rjw524]

I wouldn't use image recognition to automate Excel. There are built in XL functions to do that, and you can augment those with VBScript to directly control and access everything in excel without automating the UI.

You're right, however, I'm not good enough with that stuff yet. Still a programming newbie over here.

Some brief coding examples of how to combine some VBScript with the XL Functions perhaps would be awesome if there were any that were applicable to what I'm trying to do.

(Also, not to nitpick or anything, lol, but while I appreciate the replies, neither actually attempted to fix the code I submitted.)

[JRL]

(Also, not to nitpick or anything, lol, but while I appreciate the replies, neither actually attempted to fix the code I submitted.)

Not the kind of comment that's going to win you friends on this or any forum. Part of the reason that no one has tried to fix your code is that no one with knowledge of using Macro Scheduler would do this the way you're doing it. Also, though the description you've provided may make good sense to you, I've re-read it a number of times and I'm having difficulty wrapping my little brain around what you are trying to do and what your problem might be.

That said. Try something like this. Forget the image recognition, its unnecessary for working with Excel, Acquiring and parsing data is a much more robust and trouble free method to use. So, Starting where you do "a simple CTRL+SHIFT+PRESS DOWN command" (was that in the FIRST or the LAST sheet? Seems like it should be LAST but you say FIRST.) and assuming its the data in the LAST sheet. After highlighting using CTRL+SHIFT+PRESS DOWN, rather than use image recognition, put this data block to the clipboard using Ctrl + c like this:

Code: Select all

Press ctrl
Send>c
Release Ctrl

Instead of image recognition, retrieve the data from the clipboard and test for "current" like this:

Code: Select all

GetClipBoard>vData
UpperCase>vData,vData
Separate>vData,CURRENT,Part
If>Part_Count>1
  //"current" was found in the clipboard text (thus in excel)
  //do what you would do if found
Else
  //"current" was not found
  //Move to sheet FIRST,  move over two columns and type "Not Found"
EndIf

Some brief coding examples of how to combine some VBScript with the XL Functions perhaps would be awesome if there were any that were applicable to what I'm trying to do.

Search the forum and Marcus' blog for "VBScript Excel".

Hope this is helpful.
Dick

[rjw524]

This was VERY, VERY helpful JRL. Thanks a lot! May seem basic to a lot of regulars here, but to a non-programmer/vb user like myself, it's discovering fire! lol