asked on

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

Phillip Burton

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

Professor J

ASKER

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.

Phillip Burton

The above code copies the formula, then copies the value from the formula, so it will only calculate one row at a time.

Phillip Burton

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.

Professor J

ASKER

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.

Phillip Burton

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

Professor J

ASKER

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.

Phillip Burton

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?

Professor J

ASKER

Sorry not zeros, spaces. I mistyped space with zeros

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Professor J

ASKER

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 ?

Professor J

ASKER

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.

Phillip Burton

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.