Excel Vba

Hello,

i have the two files that i put array formulas regularly by updating the Main.xlsm from Data.xlsx.
please see the attached files.   four columns from Data headers are highlihgted in yellow to be matched with the data in Main file and return all those highlighted columns to the Main file highlighted columns.  

i wanted to have this automated by vba, i was wondering if any expert in VBA could help.

thanks.
MAIN.xlsm
DATA.xlsx
LVL 27
ProfessorJimJamAsked:
Who is Participating?
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Why VBA? If you have something that is working in Excel that is not too difficult to maintain, why would you want it in VBA, where it is less easy?

Here's the code, which just replicates your formulas:

Sheets("Main").Select
For introw = 2 To 50
    Cells(introw, 10).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!R" & introw & "C3:R51C3,MATCH(RC2&RC1,[DATA.xlsx]File!R" & introw & "C3:R51C3&[DATA.xlsx]File!R" & introw & "C5:R51C5,0)),""Not Found"")"
    Cells(introw, 11).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!R" & introw & "C5:R51C5,MATCH(RC2&RC1,[DATA.xlsx]File!R" & introw & "C3:R51C3&[DATA.xlsx]File!R" & introw & "C5:R51C5,0)),""Not Found"")"
    Cells(introw, 12).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!R" & introw & "C7:R51C7,MATCH(RC2&RC1,[DATA.xlsx]File!R" & introw & "C3:R51C3&[DATA.xlsx]File!R" & introw & "C5:R51C5,0)),""Not Found"")"
    Cells(introw, 13).FormulaArray = "=IFERROR(INDEX([DATA.xlsx]File!R" & introw & "C12:R51C12,MATCH(RC2&RC1,[DATA.xlsx]File!R" & introw & "C3:R51C3&[DATA.xlsx]File!R" & introw & "C5:R51C5,0)),""Not Found"")"
    Range(Cells(introw, 10), Cells(introw, 13)).Formula = Range(Cells(introw, 10), Cells(introw, 13)).Value
Next

Open in new window

0
 
ProfessorJimJamAuthor Commented:
VBA is required becuase the range of data is not always 50, it can be hundreds of rows. the attachments were just a sample.
i also do not want the formula to be placed in the cells but instead just the values. becuase having these array formulas will make the worksheet very slow.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The above code copies the formula, then copies the value from the formula, so it will only calculate one row at a time.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The above code copies the formula for one row, then copies the value from the formula, so it will only calculate one row at a time.

You can adjust the number of rows but adjusting the number in line 2.
0
 
ProfessorJimJamAuthor Commented:
also your code did not produce the correct result.

perhaps somthing wrong with the ,[DATA.xlsx]File!R" & introw & part. becuase with each cell moving down, it also excludes the previous rows.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Try this code then. It continues until column A is blank.

Sheets("Main").Select
introw = 2
Do Until Cells(introw, 1) = ""
    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

Open in new window

0
 
ProfessorJimJamAuthor Commented:
thanks Phillip

there is one last thing to have it fix. somthings in Main file the columns A and B names might have leading and trailing zeros. how this could be modifies to remove the leading and trailing zeros from Column A and B of the main file, before the formula is placed.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you give me an example - would column A and B be 00Hawkins or Benjamin00?

Should there be any zeros in column A or B?
0
 
ProfessorJimJamAuthor Commented:
Sorry not zeros, spaces. I mistyped space with zeros
0
 
ProfessorJimJamAuthor Commented:
Philip,

the MATCH($B" & introw & "&$A" & introw are expandable, whereas the rows in DATA.xlsx]File!$C$2:$C$51 is only stuck to the 51 row.  how do i make this DATA.xlsx]File!$C$2:$C$51   expandable ?
0
 
ProfessorJimJamAuthor Commented:
one more encountered, when there is a empy row in between the column a then the doevent stops there, while i want it to go through the last row that has data and ignore if the blank rows are in between.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
1. Change the number $51 to a suitable number.
2. Change line 3 to read
Do Until introw = 9999

Open in new window

or a suitable number.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.