Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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
0
ProfessorJimJam
Asked:
ProfessorJimJam
  • 7
  • 6
1 Solution
 
Phillip BurtonCommented:
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
 
Phillip BurtonCommented:
The above code copies the formula, then copies the value from the formula, so it will only calculate one row at a time.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Phillip BurtonCommented:
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 BurtonCommented:
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 BurtonCommented:
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
 
Phillip BurtonCommented:
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
 
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 BurtonCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now