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
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