Help with Find method for excel (COM) Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
Art365
Posts: 15
Joined: 25 Aug 2017, 08:53

Help with Find method for excel (COM)

17 May 2018, 22:53

Hello,

In part of my script I am trying to find cells with specific format, but the "format" argument of Find doesn't seem to do anything -- the method finds any cell with specified value, even though the format doesn't match. Here's my code:

Code: [Select all] [Download] GeSHi © Codebox Plus

xl := ComObjActive("Excel.Application")
sheet := xl.ActiveSheet
xl.FindFormat.Interior.Color := 65535 ;let's say I only care about cells with yellow fill
found := sheet.Cells.Find("Test", sheet.Range("A1"), -4123 , 2 , 1 , 1 , 1 , 1)
MsgBox % found.Address


Would appreciate your help!
FanaticGuru
Posts: 1064
Joined: 30 Sep 2013, 22:25

Re: Help with Find method for excel (COM)  Topic is solved

18 May 2018, 00:16

Art365 wrote:Hello,

In part of my script I am trying to find cells with specific format, but the "format" argument of Find doesn't seem to do anything -- the method finds any cell with specified value, even though the format doesn't match. Here's my code:

Code: [Select all] [Download] GeSHi © Codebox Plus

xl := ComObjActive("Excel.Application")
sheet := xl.ActiveSheet
xl.FindFormat.Interior.Color := 65535 ;let's say I only care about cells with yellow fill
found := sheet.Cells.Find("Test", sheet.Range("A1"), -4123 , 2 , 1 , 1 , 1 , 1)
MsgBox % found.Address


Would appreciate your help!

Code: [Select all] [Download] (Untitled.ahk)GeSHi © Codebox Plus

xl := ComObjActive("Excel.Application")
sheet := xl.ActiveSheet
xl.FindFormat.Clear
xl.FindFormat.Interior.Color := 65535 ;let's say I only care about cells with yellow fill
found := sheet.Cells.Find("Test", sheet.Range("A1"), -4123 , 2 , 1 , 1 , 1 , 1, ComObject(0xB,-1))
MsgBox % found.Address
It is good practice to clear the previous FindFormat each time unless you are sure what it is currently set. You can stack a lot of different format choices. Or even if the user has done a manual Find in Excel, it can mess up your code if you don't clear any previous FindFormat information.

Most important though, you were missing the last parameter which is the SearchFormat parameter. It needs to be a COM boolean type equal to true.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
Art365
Posts: 15
Joined: 25 Aug 2017, 08:53

Re: Help with Find method for excel (COM)

18 May 2018, 07:52

Thank you so much! Works like a charm.

Return to “Ask For Help”

Who is online

Users browsing this forum: chef423, llinfeng, mynameisjohn, osuki9x and 31 guests