Trying to Find ALL cells which contains x on excel Topic is solved

[fenchai]

let's say we have a random excel file:

• No idea about what the layout looks like.

• No idea what it contains.

• you want to search for all cells containing "AHK" in there and get their values or addresses.

• I do NOT want to use a specific Range as we do not know about the layout of the files.

This is the basic usage of .Find() method https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

Code: Select all

findit := xl.Cells.Find("AHK")
msgbox % findit.Value

I have found some info on the .FindNext() https://docs.microsoft.com/en-us/office/vba/api/excel.range.findnext method but I have no idea how to use it.

This is a script I was testing and gave up on.

Code: Select all

xl := Excel_Get()

findWhat := "31112"
;.Address[0,0]
findit := xl.Cells.Find(findWhat) ; if findit is empty then it didn't find else u get the address
msgbox % findit.Value
if (findit) {
	nextFind := xl.Cells.FindNext()
	While (nextFind) {
		nextFind := xl.Cells.FindNext()
		msgbox % nextFind.Value
	}
}


; Excel_Get by jethrow (modified)
; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; Github:   https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
Excel_Get(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "XLMAIN")
        return "Window class mismatch."
    ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd."
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
        return "Error calling AccessibleObjectFromWindow."
    window := ComObject(9, pacc, 1)
    if ComObjType(window) != 9
        return "Error wrapping the window object."
    Loop
        try return window.Application
        catch e
            if SubStr(e.message, 1, 10) = "0x80010001"
                ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
            else
                return "Error accessing the application object."
}

[flyingDman]

Slight change to that last script:

Code: Select all

xl := Excel_Get()
findWhat := "AHK"
findit := xl.Cells.Find(findWhat) 									; if findit is empty then it didn't find else u get the address
msgbox % findit.Value " at " findit.address 

if (findit) {
	nextFind := xl.Cells.FindNext()
	While (nextFind) {
		nextFind := xl.Cells.FindNext(nextfind)
		if (nextfind.address = findit.address)                          ; <<<<
			break
		msgbox % nextFind.Value " at " nextfind.address
	}	
}

[fenchai]

Slight change to that last script:

Code: Select all

xl := Excel_Get()
findWhat := "AHK"
findit := xl.Cells.Find(findWhat) 									; if findit is empty then it didn't find else u get the address
msgbox % findit.Value " at " findit.address 

if (findit) {
	nextFind := xl.Cells.FindNext()
	While (nextFind) {
		nextFind := xl.Cells.FindNext(nextfind)
		if (nextfind.address = findit.address)                          ; <<<<
			break
		msgbox % nextFind.Value " at " nextfind.address
	}	
}

Impressive!