• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

excel vba

currently this code 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.

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

Open in new window

  • 3
  • 2
1 Solution
Rob HensonFinance AnalystCommented:
You can discover the last row by selecting A1048576 and then doing an xlEndUp.

There is a way of doing it without selecting first but I can't recall the syntax.

You could use this command to discover the last row in both files and then set them to two variables (lastrow1 & lastrow2)to use within the routine.

Your Do Until would then be

Do Until introw = lastrow1

Your array formulas would then be:

Cells(introw, 10).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!$C$2:$C$"&lastrow2&",MATCH($B" & introw & "&$A" & introw & ",[DATA.xlsx]File!$C$2:$C$"&lastrow2&"&[DATA.xlsx]File!$E$2:$E$"&lastrow2&",0)),""Not Found"")"

Rob H
Rob HensonFinance AnalystCommented:

LastRow = Range("A1048576", Selection.End(xlUp)).Row
ProfessorJimJamAuthor Commented:
yes. it could be LastRow = Cells(Rows.Count, 1).End(xlUp).Row
ProfessorJimJamAuthor Commented:
my immediate window does not work. any idea why it was working before but after running the macro once it does not return any information.

when i put print introw   nothing returns
also i tried  with ?introw

suddenly it stopped working.  is there somthign i did wrong?
Rob HensonFinance AnalystCommented:
Sorry, don't know anything about immediate window.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now