vba question

[jamilm]

Gurus,

appreciate if anyone would help changing this code, currently it works via doevent, the problem is if there is blank cell in column A then it stop there, however the even if there is a blank cell , the last row is down further. i want this code so that it works to the last row that has data.

second problem is the ",[DATA.xlsx]File!$C$2:$C$51 and ",[DATA.xlsx]File!$E$2:$E$51 you can see the it looks up to 51 rows in the next sheet and it is not dynamic too. i want this also to look at the last row instead of 51.

Sheets("Main").Select
introw = 2
Do Until Cells(introw, 1) = ""
    Cells(introw, 1) = Trim(Cells(introw, 1))
    Cells(introw, 2) = Trim(Cells(introw, 2))
    Cells(introw, 10).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!$C$2:$C$51,MATCH($B" & introw & "&$A" & introw & ",[DATA.xlsx]File!$C$2:$C$51&[DATA.xlsx]File!$E$2:$E$51,0)),""Not Found"")"
    Cells(introw, 11).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!$E$2:$E$51,MATCH($B" & introw & "&$A" & introw & ",[DATA.xlsx]File!$C$2:$C$51&[DATA.xlsx]File!$E$2:$E$51,0)),""Not Found"")"
    Cells(introw, 12).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!$G$2:$G$51,MATCH($B" & introw & "&$A" & introw & ",[DATA.xlsx]File!$C$2:$C$51&[DATA.xlsx]File!$E$2:$E$51,0)),""Not Found"")"
    Cells(introw, 13).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!$L$2:$L$51,MATCH($B" & introw & "&$A" & introw & ",[DATA.xlsx]File!$C$2:$C$51&[DATA.xlsx]File!$E$2:$E$51,0)),""Not Found"")"
    Range(Cells(introw, 10), Cells(introw, 13)).Formula = Range(Cells(introw, 10), Cells(introw, 13)).Value
    introw = introw + 1
    DoEvents
Loop

 

[jamilm]

i sorted this by myself.