Excel not getting all info?

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
Bad husband
Posts: 54
Joined: 21 Oct 2017, 13:38

Excel not getting all info?

Post by Bad husband » 29 Nov 2022, 21:01

I have a workbook with 2 sheets. (weekly and yearly)

I need to take info from the weekly and create a formula to the yearly sheet.

I've taken some code and it lists the column name of the info, but right now it is populating the last info on all of the rows

Image

It is populating only the info from the 21x200ml on all of the rows in the yearly sheet. Not sure where my code is broken?

Thanks

Code: Select all

#a::
xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
myArray := [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]

leng := myArray.Length()

i := 1
while(i <= leng)
{
	v := myArray[i]
	;MsgBox, myArray[%i%] = %v%
	var := % v
	;MsgBox, % var " :var"
	i := i+1
		
	{
		Loop, 2
			
		MyRange := xl.Sheets("Page1").Range("B1:F1")
		FoundCell := xl.Sheets("Page1").Range("B1:F1").Find(v)
		;MsgBox, % FoundCell.Offset(0, 1).Value
		;MsgBox, % FoundCell "-FoundCell"
		FirstAddr := FoundCell.Row  ; The while-loop below will exit when it reaches this cell.
		FirstAddr := FoundCell.column  ; The while-loop below will exit when it reaches this cell.
		colm := xl.ActiveSheet.cells(1,FirstAddr).address
		
		RegExMatch(colm, "\$(.*)\$",matcha)
		;MsgBox % matcha1  " matcha1"
		;MsgBox, % "The first found cell in the range is " FirstAddr " with a value of '" FoundCell.Value "'."
				
		if (FoundCell.Value = "")
			Break				
				
	    if var := "62001- 24X200ML"			
		xl.Sheets("YTD").Range("B42").Formula := "=Page1!" matcha1 "2" 
		xl.Sheets("YTD").Range("B43").Formula := "=Page1!" matcha1 "3"	
	    if var := "62002- 32X200ML"			
		xl.Sheets("YTD").Range("C42").Formula := "=Page1!" matcha1 "2" 
	        xl.Sheets("YTD").Range("C43").Formula := "=Page1!" matcha1 "3"	
	    if var := "62003- 50X200ML"			
		xl.Sheets("YTD").Range("D42").Formula := "=Page1!" matcha1 "2" 
                xl.Sheets("YTD").Range("D43").Formula := "=Page1!" matcha1 "3"
	    if var := "62004- 40X200ML"			
		xl.Sheets("YTD").Range("E42").Formula := "=Page1!" matcha1 "2" 
                xl.Sheets("YTD").Range("E43").Formula := "=Page1!" matcha1 "3"
	    if var := "62005- 21X200ML"			
		xl.Sheets("YTD").Range("F42").Formula := "=Page1!" matcha1 "2" 
                xl.Sheets("YTD").Range("F43").Formula := "=Page1!" matcha1 "3"			
				
		}}
	 	 	 Return

kintar0e
Posts: 41
Joined: 05 Mar 2019, 07:32

Re: Excel not getting all info?

Post by kintar0e » 29 Nov 2022, 21:47

I think position of bracket { in loop is not correct.
so with := you are storing data. for comparing is a simple =

Code: Select all

#a::
xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
myArray := [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]

leng := myArray.Length()
i := 1

while(i <= leng)
{
	v := myArray[i]
	i := i+1
	
	Loop, 2
	{	
		MyRange := xl.Sheets("Page1").Range("B1:F1")
		FoundCell := xl.Sheets("Page1").Range("B1:F1").Find(v)

		;MsgBox, % FoundCell.Offset(0, 1).Value
		;MsgBox, % FoundCell "-FoundCell"
		FirstAddr := FoundCell.Row  ; The while-loop below will exit when it reaches this cell.
		FirstAddr := FoundCell.column  ; The while-loop below will exit when it reaches this cell.
		colm := xl.ActiveSheet.cells(1,FirstAddr).address
		
		RegExMatch(colm, "\$(.*)\$",matcha)

		; MsgBox % matcha1  " matcha1"
		; MsgBox, % "The first found cell in the range is " FirstAddr " with a value of '" FoundCell.Value "'."
				
		if (FoundCell.Value = "")
			Break				
				
	    if v = "62001- 24X200ML"
	    {
			xl.Sheets("YTD").Range("B42").Formula := "=Page1!" matcha1 "2" 
			xl.Sheets("YTD").Range("B43").Formula := "=Page1!" matcha1 "3"
	    }
	    
	    if v = "62002- 32X200ML"
	    {
			xl.Sheets("YTD").Range("C42").Formula := "=Page1!" matcha1 "2" 
	        xl.Sheets("YTD").Range("C43").Formula := "=Page1!" matcha1 "3"
	    }

	    if v = "62003- 50X200ML"
	    {
			xl.Sheets("YTD").Range("D42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("D43").Formula := "=Page1!" matcha1 "3"
        }

	    if v = "62004- 40X200ML"
	    {
			xl.Sheets("YTD").Range("E42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("E43").Formula := "=Page1!" matcha1 "3"
	    }

	    if v = "62005- 21X200ML"
	    {
			xl.Sheets("YTD").Range("F42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("F43").Formula := "=Page1!" matcha1 "3"
	    }			
				
	}
}
Return

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel not getting all info?

Post by flyingDman » 29 Nov 2022, 22:21

You can greatly simplify and refine the code using a for-loop, copy, and offset:

Code: Select all

myArray := [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]
xl := ComObjActive("Excel.Application")
for a,b in [1,2]  
	for x,y in myArray
		xl.Sheets("Page1").usedrange.Find(y).offset(b,0).copy(xl.Sheets("YTD").range("A" 41 + b).offset(0,x))
see also this post: viewtopic.php?t=109635&p=487443 where the target range also had labels like in your array.
14.3 & 1.3.7

Bad husband
Posts: 54
Joined: 21 Oct 2017, 13:38

Re: Excel not getting all info?

Post by Bad husband » 29 Nov 2022, 22:38

kintar0e wrote:
29 Nov 2022, 21:47
I think position of bracket { in loop is not correct.
so with := you are storing data. for comparing is a simple =

Code: Select all

#a::
xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
myArray := [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]

leng := myArray.Length()
i := 1

while(i <= leng)
{
	v := myArray[i]
	i := i+1
	
	Loop, 2
	{	
		MyRange := xl.Sheets("Page1").Range("B1:F1")
		FoundCell := xl.Sheets("Page1").Range("B1:F1").Find(v)

		;MsgBox, % FoundCell.Offset(0, 1).Value
		;MsgBox, % FoundCell "-FoundCell"
		FirstAddr := FoundCell.Row  ; The while-loop below will exit when it reaches this cell.
		FirstAddr := FoundCell.column  ; The while-loop below will exit when it reaches this cell.
		colm := xl.ActiveSheet.cells(1,FirstAddr).address
		
		RegExMatch(colm, "\$(.*)\$",matcha)

		; MsgBox % matcha1  " matcha1"
		; MsgBox, % "The first found cell in the range is " FirstAddr " with a value of '" FoundCell.Value "'."
				
		if (FoundCell.Value = "")
			Break				
				
	    if v = "62001- 24X200ML"
	    {
			xl.Sheets("YTD").Range("B42").Formula := "=Page1!" matcha1 "2" 
			xl.Sheets("YTD").Range("B43").Formula := "=Page1!" matcha1 "3"
	    }
	    
	    if v = "62002- 32X200ML"
	    {
			xl.Sheets("YTD").Range("C42").Formula := "=Page1!" matcha1 "2" 
	        xl.Sheets("YTD").Range("C43").Formula := "=Page1!" matcha1 "3"
	    }

	    if v = "62003- 50X200ML"
	    {
			xl.Sheets("YTD").Range("D42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("D43").Formula := "=Page1!" matcha1 "3"
        }

	    if v = "62004- 40X200ML"
	    {
			xl.Sheets("YTD").Range("E42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("E43").Formula := "=Page1!" matcha1 "3"
	    }

	    if v = "62005- 21X200ML"
	    {
			xl.Sheets("YTD").Range("F42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("F43").Formula := "=Page1!" matcha1 "3"
	    }			
				
	}
}
Return
your code didn't work for me. Nothing was copied to the YTD sheet?

Bad husband
Posts: 54
Joined: 21 Oct 2017, 13:38

Re: Excel not getting all info?

Post by Bad husband » 29 Nov 2022, 22:50

flyingDman wrote:
29 Nov 2022, 22:21
You can greatly simplify and refine the code using a for-loop, copy, and offset:

Code: Select all

myArray := [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]
xl := ComObjActive("Excel.Application")
for a,b in [1,2]  
	for x,y in myArray
		xl.Sheets("Page1").usedrange.Find(y).offset(b,0).copy(xl.Sheets("YTD").range("A" 41 + b).offset(0,x))
see also this post: viewtopic.php?t=109635&p=487443 where the target range also had labels like in your array.
Hi flyingDman, your code works but in reality the YTD sheet the corresponding info are not beside each other so a straight paste in not possible. Also I was really looking to create a formula that will reference back to the weekly sheet.

I will keep on trying to get it to work. Thanks again for your help

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Excel not getting all info?

Post by flyingDman » 29 Nov 2022, 23:12

info are not beside each other
. Explain. Give an example.
formula that will reference back to the weekly sheet
. Same
14.3 & 1.3.7

Bad husband
Posts: 54
Joined: 21 Oct 2017, 13:38

Re: Excel not getting all info?

Post by Bad husband » 29 Nov 2022, 23:15

I was able to get it work. My brackets { } were in the correct location for the loop, but kintarOe suggestion about the v = was correct, but I had to remove the " " from his suggestion.

Thanks for the help

Here is the working code

Code: Select all

#a::
xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
myArray := [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]

leng := myArray.Length()

i := 1
while(i <= leng)
{
	v := myArray[i]	
	i := i+1
	
	{
		Loop, 2
			
		MyRange := xl.Sheets("Page1").Range("B1:F1")
		FoundCell := xl.Sheets("Page1").Range("B1:F1").Find(v)
		;MsgBox, % FoundCell.Offset(0, 1).Value
		;MsgBox, % FoundCell "-FoundCell"		
		FirstAddr := FoundCell.Row  ; The while-loop below will exit when it reaches this cell.
		FirstAddr := FoundCell.column  ; The while-loop below will exit when it reaches this cell.
		colm := xl.ActiveSheet.cells(1,FirstAddr).address
		
		RegExMatch(colm, "\$(.*)\$",matcha)
		;MsgBox % matcha1  " matcha1"
		;MsgBox, % "The first found cell in the range is " FirstAddr " with a value of '" FoundCell.Value "'."
		
		if (FoundCell.Value = "")
			Break
		
		if v = 62001- 24X200ML
	    {
			xl.Sheets("YTD").Range("B42").Formula := "=Page1!" matcha1 "2" 
			xl.Sheets("YTD").Range("B43").Formula := "=Page1!" matcha1 "3"
	    }
	    
	    if v = 62002- 32X200ML
	    {
			xl.Sheets("YTD").Range("C42").Formula := "=Page1!" matcha1 "2" 
	        xl.Sheets("YTD").Range("C43").Formula := "=Page1!" matcha1 "3"
	    }
		
	    if v = 62003- 50X200ML
	    {
			xl.Sheets("YTD").Range("D42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("D43").Formula := "=Page1!" matcha1 "3"
        }
		
	    if v = 62004- 40X200ML
	    {
			xl.Sheets("YTD").Range("E42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("E43").Formula := "=Page1!" matcha1 "3"
	    }
		
	    if v = 62005- 21X200ML
	    {
			xl.Sheets("YTD").Range("F42").Formula := "=Page1!" matcha1 "2" 
            xl.Sheets("YTD").Range("F43").Formula := "=Page1!" matcha1 "3"
	    }

Bad husband
Posts: 54
Joined: 21 Oct 2017, 13:38

Re: Excel not getting all info?

Post by Bad husband » 29 Nov 2022, 23:39

flyingDman wrote:
29 Nov 2022, 23:12
info are not beside each other
. Explain. Give an example.
formula that will reference back to the weekly sheet
. Same
I know that your knowledge with excel and autohotkey is excellent. I need some additional help with my working script.
There might be weeks where I don't have info for the sizes, so I ran a test were I removed one and the script stop :headwall:

I got an error on this line: colm := xl.ActiveSheet.cells(1,FirstAddr).address

How would I get passed this error?

thanks

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel not getting all info?

Post by Xeo786 » 30 Nov 2022, 03:04

Bad husband wrote:
29 Nov 2022, 23:39
flyingDman wrote:
29 Nov 2022, 23:12
info are not beside each other
. Explain. Give an example.
formula that will reference back to the weekly sheet
. Same
I know that your knowledge with excel and autohotkey is excellent. I need some additional help with my working script.
There might be weeks where I don't have info for the sizes, so I ran a test were I removed one and the script stop :headwall:

I got an error on this line: colm := xl.ActiveSheet.cells(1,FirstAddr).address

How would I get passed this error?

thanks
when you use xl.ActiveSheet means the sheet that is active right now will be accessed, for example, your code is supposed to get data from sheet1 and you are using activesheet to get data from sheet1, it will work until sheet1 is active if you use mouse and goto to sheet2 your program will try to access sheet2 and as it is programmed to follow active sheet. your whole code can be shortened, it might not work for but here is the idea

Code: Select all

xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
page1 := xl.Sheets("Page1") ; access the specific sheet
YDT := xl.Sheets("YTD") ; access the specific sheet
; we have created an object with heading with specific col
for col, v in {"B":"62001- 24X200ML", "C":"62002- 32X200ML", "D":"62003- 50X200ML", "E":"62004- 40X200ML", "F":"62005- 21X200ML"}
{
    Cell := page1.Range("B1:F1").Find(v)
    if (Cell.Value = "")
		Break
    YDT.Range(col "42").value := "=Page1!" cell.offset(1,0).address[0,0] ; adress from 
    YDT.Range(col "43").value := "=Page1!" cell.offset(2,0).address[0,0]  

 /* ;   why get links when you can get the value 
    YDT.Range(col "42").value := cell.offset(1,0).text
    YDT.Range(col "43").value := cell.offset(2,0).text
*/

/* ; you can use this regex way 
    RegExMatch(Cell.address, "\$(.*)\$",matcha)
    YDT.Range(col "42").value := "=Page1!" matcha1 "2" 
    YDT.Range(col "43").value := "=Page1!" matcha1 "3"
*/
}
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

Bad husband
Posts: 54
Joined: 21 Oct 2017, 13:38

Re: Excel not getting all info?

Post by Bad husband » 30 Nov 2022, 08:37

Xeo786 wrote:when you use xl.ActiveSheet means the sheet that is active right now will be accessed, for example, your code is supposed to get data from sheet1 and you are using activesheet to get data from sheet1, it will work until sheet1 is active if you use mouse and goto to sheet2 your program will try to access sheet2 and as it is programmed to follow active sheet. your whole code can be shortened, it might not work for but here is the idea

Code: Select all

xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
page1 := xl.Sheets("Page1") ; access the specific sheet
YDT := xl.Sheets("YTD") ; access the specific sheet
; we have created an object with heading with specific col
for col, v in {"B":"62001- 24X200ML", "C":"62002- 32X200ML", "D":"62003- 50X200ML", "E":"62004- 40X200ML", "F":"62005- 21X200ML"}
{
    Cell := page1.Range("B1:F1").Find(v)
    if (Cell.Value = "")
		Break
    YDT.Range(col "42").value := "=Page1!" cell.offset(1,0).address[0,0] ; adress from 
    YDT.Range(col "43").value := "=Page1!" cell.offset(2,0).address[0,0]  

 /* ;   why get links when you can get the value 
    YDT.Range(col "42").value := cell.offset(1,0).text
    YDT.Range(col "43").value := cell.offset(2,0).text
*/

/* ; you can use this regex way 
    RegExMatch(Cell.address, "\$(.*)\$",matcha)
    YDT.Range(col "42").value := "=Page1!" matcha1 "2" 
    YDT.Range(col "43").value := "=Page1!" matcha1 "3"
*/
}
your code works if all the values are in the table (Page1). It looks like you have hard coded the column letter to the size formats. In my example those are all the sizes we run but each weekly report it could be different.

If I have a table with only values for 62003- 50X200ML & 62005- 21X200ML, then these would be column B and C and then your code would not work.
So I would need the column letter if only if those items are present in the current table.

Thanks
Attachments
image.png
image.png (2.36 KiB) Viewed 462 times

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel not getting all info?

Post by Xeo786 » 01 Dec 2022, 03:09

Bad husband wrote:
30 Nov 2022, 08:37
It looks like you have hard coded the column letter to the size formats.
isn't your code doing the same ??
Spoiler
this Untested code is supposed to find the column according to the heading

Code: Select all

xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
SheetA := xl.Sheets("Page1") ; access the specific sheet
SheetB := xl.Sheets("YTD") ; access the specific sheet
; we have created an object with heading with specific col
for k, v in [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]
{
    Cellout := SheetA.Range("B1:F1").Find(v)
    Cellin   := SheetB.Range("B1:F1").Find(v)
    if (Cellout.Value = "")
	{
		msgbox, % "Unable to find Size: "  v " on Sheet " SheetA.name
		continue
	}

	if (Cellin.Value = "")
	{
		msgbox, % "Unable to find Size: "  v " on Sheet " SheetB.name
		continue
	}

	Cellin.offset(1,0).value "=" SheetA.name "!" Cellout.offset(1,0).address[0,0] 
	Cellin.offset(2,0).value "=" SheetA.name "!" Cellout.offset(2,0).address[0,0] 
}
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

Bad husband
Posts: 54
Joined: 21 Oct 2017, 13:38

Re: Excel not getting all info?

Post by Bad husband » 01 Dec 2022, 20:33

this Untested code is supposed to find the column according to the heading

Code: Select all

xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
SheetA := xl.Sheets("Page1") ; access the specific sheet
SheetB := xl.Sheets("YTD") ; access the specific sheet
; we have created an object with heading with specific col
for k, v in [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]
{
    Cellout := SheetA.Range("B1:F1").Find(v)
    Cellin   := SheetB.Range("B1:F1").Find(v)
    if (Cellout.Value = "")
	{
		msgbox, % "Unable to find Size: "  v " on Sheet " SheetA.name
		continue
	}

	if (Cellin.Value = "")
	{
		msgbox, % "Unable to find Size: "  v " on Sheet " SheetB.name
		continue
	}

	Cellin.offset(1,0).value "=" SheetA.name "!" Cellout.offset(1,0).address[0,0] 
	Cellin.offset(2,0).value "=" SheetA.name "!" Cellout.offset(2,0).address[0,0] 
}
your code doesn't work for the existing sheets. If I remove the quotes for k, v in [ 62001- 24X200ML, 62002- 32X200ML, 62003- 50X200ML, 62004- 40X200ML, 62005- 21X200ML] I get only the mgbox about unable to find the size and nothing is added to the YTD sheet.

I think I need to rethink this problem because my array is all the possible sizes, but on the weekly report some weeks I might have all and then the following week only 1, or 2 or 3. So it it not static, therefore the columns where the info is located would change each week.

So I need something like the following
If column heading is equal to 24X200ML, what is the column letter? The offset will not work for the rows because the info in the rows might also change. It could be in row10 one week and then the following week it might be in row15. The report changes weekly. I would have to use other find arguments to find the rows that I need to copy from Page1 to the YTD sheet

I guess I would need a loop to go thru the array.

If v = 62001- 24X200ML
xl.Sheets("YTD").Range("Q42").Formula := "=Page1!" column "2" ; what is the column letter where 62001- 24X200ML heading is
xl.Sheets("YTD").Range("R42").Formula := "=Page1!" column "17"
xl.Sheets("YTD").Range("S42").Formula := "=Page1!" column "32"

if v = 62002- 32X200ML
xl.Sheets("YTD").Range("V42").Formula := "=Page1!" column "2" ; what is the column letter where 62002- 32X200ML heading is
xl.Sheets("YTD").Range("W42").Formula := "=Page1!" column "17"
xl.Sheets("YTD").Range("X42").Formula := "=Page1!" column "32"

etc.

thanks for helping. I will keep on trying to see if I can find a solution to my problem.

User avatar
Xeo786
Posts: 759
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Excel not getting all info?

Post by Xeo786 » 02 Dec 2022, 01:19

Bad husband wrote:
01 Dec 2022, 20:33
this Untested code is supposed to find the column according to the heading

Code: Select all

xl := ComObjActive("Excel.Application") ; Gets an open instance of the excel application
SheetA := xl.Sheets("Page1") ; access the specific sheet
SheetB := xl.Sheets("YTD") ; access the specific sheet
; we have created an object with heading with specific col
for k, v in [ "62001- 24X200ML", "62002- 32X200ML", "62003- 50X200ML", "62004- 40X200ML", "62005- 21X200ML"]
{
    Cellout := SheetA.Range("B1:F1").Find(v)
    Cellin   := SheetB.Range("B1:F1").Find(v)
    if (Cellout.Value = "")
	{
		msgbox, % "Unable to find Size: "  v " on Sheet " SheetA.name
		continue
	}

	if (Cellin.Value = "")
	{
		msgbox, % "Unable to find Size: "  v " on Sheet " SheetB.name
		continue
	}

	Cellin.offset(1,0).value "=" SheetA.name "!" Cellout.offset(1,0).address[0,0] 
	Cellin.offset(2,0).value "=" SheetA.name "!" Cellout.offset(2,0).address[0,0] 
}
your code doesn't work for the existing sheets. If I remove the quotes for k, v in [ 62001- 24X200ML, 62002- 32X200ML, 62003- 50X200ML, 62004- 40X200ML, 62005- 21X200ML] I get only the mgbox about unable to find the size and nothing is added to the YTD sheet.

I think I need to rethink this problem because my array is all the possible sizes, but on the weekly report some weeks I might have all and then the following week only 1, or 2 or 3. So it it not static, therefore the columns where the info is located would change each week.

So I need something like the following
If column heading is equal to 24X200ML, what is the column letter? The offset will not work for the rows because the info in the rows might also change. It could be in row10 one week and then the following week it might be in row15. The report changes weekly. I would have to use other find arguments to find the rows that I need to copy from Page1 to the YTD sheet

I guess I would need a loop to go thru the array.

If v = 62001- 24X200ML
xl.Sheets("YTD").Range("Q42").Formula := "=Page1!" column "2" ; what is the column letter where 62001- 24X200ML heading is
xl.Sheets("YTD").Range("R42").Formula := "=Page1!" column "17"
xl.Sheets("YTD").Range("S42").Formula := "=Page1!" column "32"

if v = 62002- 32X200ML
xl.Sheets("YTD").Range("V42").Formula := "=Page1!" column "2" ; what is the column letter where 62002- 32X200ML heading is
xl.Sheets("YTD").Range("W42").Formula := "=Page1!" column "17"
xl.Sheets("YTD").Range("X42").Formula := "=Page1!" column "32"

etc.

thanks for helping. I will keep on trying to see if I can find a solution to my problem.
I suspect you could have used Hlookup instead using AHK.
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

Post Reply

Return to “Ask for Help (v1)”