Retriving all cell of a column in an Excel files that contain a specific value Topic is solved

[Mannaia666]

Hello there!
i'm writing a script and i need to retrive the position of a specific cell, if find take data from Column B and C and display them,
i've found this:

Code: Select all

xl := ComObjCreate("Excel.Application")
xl.Visible := True  ; Rendi Excel visibile
workbook := xl.Workbooks.Open(Filename)  
worksheet := workbook.Sheets(1)  

foundCell := worksheet.Range("A:A").Find(DateLookUp)

if (foundCell)
{
    ColumnB := worksheet.Cells(foundCell.Row, 2).Value
    ColumnC := worksheet.Cells(foundCell.Row, 3).Value
    MsgBox, Found `n%ColumnB% `n%ColumnC%
}
else
    MsgBox, nothing found.
workbook.Close(false)
xl.Quit()

this script work just fine, but now i have to retrive the next occurrence of the same value, how can i do it?
I'm not very comfortable with arrays, can someone help me with a little explanation? (if it's possible in italian woud be a plus )

[flyingDman]

Example:

Code: Select all

DateLookUp := "foobar"
xl := ComObjCreate("Excel.Application")
xl.Visible := True  ; Rendi Excel visibile
xl.Workbooks.Open(Filename)  
rng := xl.sheets(1).range("A:A")
foundCell := rng.Find(DateLookUp) 
msgbox % "found`n" foundCell.offset(0,1).Value "`n" foundCell.offset(0,2).value

if (foundCell) 
	{
	nextFind := foundcell
	While (nextFind) 
		{
		nextFind := rng.FindNext(nextfind)
		if (nextfind.address = foundCell.address)
			break
		msgbox % "found`n" nextfind.offset(0,1).Value "`n" nextfind.offset(0,2).value
		}	
	}

No arrays used (anyway, my Italian is rusty ... )

Find and FindNext are fine but this is another way:

Code: Select all

DateLookUp := "foobar"
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns("A").cells
	if (c.value = DateLookUp)
		lst .= c.offset(0,1).Value "`t" c.offset(0,2).value "`n"
msgbox % lst

[Mannaia666]

Example:

Code: Select all

DateLookUp := "foobar"
xl := ComObjCreate("Excel.Application")
xl.Visible := True  ; Rendi Excel visibile
xl.Workbooks.Open(Filename)  
rng := xl.sheets(1).range("A:A")
foundCell := rng.Find(DateLookUp) 
msgbox % "found`n" foundCell.offset(0,1).Value "`n" foundCell.offset(0,2).value

if (foundCell) 
	{
	nextFind := foundcell
	While (nextFind) 
		{
		nextFind := rng.FindNext(nextfind)
		if (nextfind.address = foundCell.address)
			break
		msgbox % "found`n" nextfind.offset(0,1).Value "`n" nextfind.offset(0,2).value
		}	
	}

No arrays used (anyway, my Italian is rusty ... )

Find and FindNext are fine but this is another way:

Code: Select all

DateLookUp := "foobar"
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns("A").cells
	if (c.value = DateLookUp)
		lst .= c.offset(0,1).Value "`t" c.offset(0,2).value "`n"
msgbox % lst

Wonderful, i just have to understand what have you done so i can adjust for my next part of the script. Thanks!
i'm thinking about how to know in advance how many occurrency there will be, my idea is to add a counter and let the script parse the entire column, and at every occurrency save the row in a var and ad 1 to the counter, then restart and loop use the row saved in the var... maybe there is a better way...

[flyingDman]

Here is were the arrays come in. Try this:

Code: Select all

arr := []
DateLookUp := "foobar"
xl := ComObjActive("excel.application")
for c in xl.activesheet.usedrange.columns("A").cells
	if (c.value = DateLookUp)
		arr.push([c.offset(0,1).Value,c.offset(0,2).value])
msgbox % arr.1.1

The arr you just created has as many rows as you spreadsheet data and 2 columns. You can retrieve the elements using arr.rownumber.columnnumber.

[Mannaia666]

Thanks, Here is were the arrays come in and my brain goes out
i think the main problem is that i don't know the basic operation with array, and everytime i search for a tutorial i only find either hyper-complex one or hyper-semplified tutorial that didn't give me a real use.
did someone have a tutorial that can be shared?

[mikeyww]

I did the following.

1. Navigated to tutorials board.
2. Searched for "arrays".

viewtopic.php?f=7&t=29232

3. Looked at the Arrays page of the documentation.

[Chunjee]

for arrays I find the for command very useful.

It will go through each element in the array

https://www.autohotkey.com/docs/v1/lib/For.htm

Code: Select all

colors := ["red", "green", "blue"]
for key, value in colors {
    msgbox, % value 
}
; will msgbox "red", then "green", then "blue"

the keys are not used in the above code but they would be 1, then 2, then 3
If you want to change the values in your array you would need to use colors[key] := newValue

but I think you just want to match excel values so probably if (value = searchValue)

[mikeyww]

That's helpful.

I think, too, that it helps to start by thinking about a simple array and what it represents.

What is a simple array?

One could think of a simple array as an ordered stack of things inside a container. The container name is the array name. In a simple array, we use brackets to refer to a thing by its position in the stack.

I can define my container's things, and I can add new things to the container, too.

For loops through the things.

Code: Select all

#Requires AutoHotkey v1.1.33
container := ["thing1", "thing2", "thing3"]      ; Three things in the container
MsgBox % container[2]                            ; This is the second thing
container.Push("thing4")                         ; Add another thing
MsgBox % container[4]                            ; Show it to me
For n, thing in container                        ; Loop through the things
 MsgBox,, % "N = " n, % "Thing " n ":`n`n" thing ; Show me each thing, one at a time

In each iteration, n is the position (more broadly: key), and thing is the variable containing the value of the thing in that position.

A comparable loop is below.

Code: Select all

Loop 4
 MsgBox,, % "N = " A_Index, % "Thing " A_Index ":`n`n" container[A_Index]

How many things are there?

Code: Select all

MsgBox % container.Length()

[Mannaia666]

i wold like to thanks you all, that will be a lot of things to read (and a lot of script to tune-up).