Index match array for multiple cells

[nanunoran]

Hi,
I just learnt how to do INDEX MATCH ARRAYS. (to populate my report I need to match multiple rows and columns from source sheet to import data).
Now I am trying to replicate same in VBA. (for this example row1&2 & column1&2 on both seed(source) and result(one I am trying to populate) sheets).

I wrote the code below that works just fine for 1 CELL.

Sub Button1_Click()
Range("C4").FormulaArray = "=INDEX('SEED'!$A$1:$f$6,MATCH(A4&B4,'SEED'!$A$1:$A$6&'SEED'!$B$1:$B$6,0),3)"
End Sub

I know in excel I can simply drag the formula across rows/columns to populate them automatically and the way I would do this in VBA would be by creating loops. However when I cant figure out how to write the code, again I am total noob to VBA programmin and this question seems more like learning syntax so can anyone help me learn & achieve this?

Thanks.

Here is what I have that needs improvement

Sub Button1_Click()
Dim iRow As Integer
For iRow = 3 To 4
Range("C" & iRow).FormulaArray = "=INDEX('SEED'!$A$1:$f$6,MATCH(AiRow & iRow,'SEED'!$A$1:$A$6&'SEED'!$B$1:$B$6,0),3)"
Next iRow
End Sub

here iRow is to identify row number, and to keep simple I am only doing 2 rows. but what I cant figure out is how do i write MATCH statement to identify rows needed to be matched from SEED sheet?

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Here's an example of syntax that may be used to place the value of VBA variables in worksheet formulas....

Sub Button1_Click()
 Dim iRow As Integer
 
 For iRow = 3 To 4
   Range("C" & iRow).FormulaArray = _
      "=INDEX('SEED'!$A$1:$f$6,MATCH(A" & iRow & "&B" & iRow & _
         ",'SEED'!$A$1:$A$6&'SEED'!$B$1:$B$6,0),3)"
 Next iRow
End Sub

 

[nanunoran]

Thanks, it worked like charm. I will post my final code to see if it can be made more efficient.

 

[nanunoran]

Sorry got stuck again

Now that I resolved for for incremental Rows, I was trying to fill in all columns as well.

Sub Button1_Click()
Dim iCol, iRow As Integer
For iCol = 3 To 4
For iRow = 3 To 4
Range("d" & iRow).FormulaArray = "=INDEX('SEED'!$A$1:$f$6,MATCH(A" & iRow & "&B" & iRow & ",'SEED'!$A$1:$A$6&'SEED'!$B$1:$B$6,0), & iCol &)"
Next iRow
Next iCol
End Sub

and I got error there, I really want to learn how the structure works in VBA, ( how do i know where to use " or & or some combination of 2 or whatever else I can) Would you please refer me to some guide as well and also tell me how do i fix it?
Thanks.

 

[nanunoran]

Better yet rather than having range("d" & iRow) which would only populate column d, i am trying to create loop to populate all columns in spefieid range by iCol, here is how my attempt look like

Cells(iCol, iRow).FormulaArray = "=INDEX('SEED'!$A$1:$f$6,MATCH(A" & iRow & "&B" & iRow & ",'SEED'!$A$1:$A$6&'SEED'!$B$1:$B$6,0),MATCH(" & iCol & "A & " & iCol & "B,'SEED'!$A$1:$F$1&'SEED'!$A$2:$F$2,0)"

but ofcourse it throws error, what do i need to correct?

 

[Jerry Sullivan]

I understand the gist of what you are trying to do, but I'm not sure the specific formula you're trying to convert into VBA.

You can probably make that yourself using this technique, assuming you already have an example formula that works.

Using the example from your Original Post, start with the worksheet formula that works in a cell...
=INDEX(SEED!$A$1:$F$6,MATCH(A4&B4,SEED!$A$1:$A$6&SEED!$B$1:$B$6,0),3)

Make a VBA expression with single quotes around the entire formula.
Range("C4").FormulaArray ="=INDEX(SEED!$A$1:$F$6,MATCH(A4&B4,SEED!$A$1:$A$6&SEED!$B$1:$B$6,0),3)"

For each character or string that you want to replace with a variable, replace it with: " & variable & "

Range("C4").FormulaArray ="=INDEX(SEED!$A$1:$F$6,MATCH(A" & iRow & "&B" & iRow & ",SEED!$A$1:$A$6&SEED!$B$1:$B$6,0),3)"

Try applying the same technique to replace the characters in your desired formula.

 

[nanunoran]

Thans for explanation and I with your help I DID FIGURE OUT how to increment both rows and columns in loops.
Below is the code that worked just fine:

Sub Button1_Click()
Dim iCol, iRow As Integer
Dim n As String

For iCol = 4 To 6
For iRow = 3 To 4
n = Chr(iCol + 64)

Cells(iRow, iCol).FormulaArray = "=INDEX('S'!$A$1:$f$6,MATCH(A" & iRow & "&B" & iRow & ",'S'!$A$1:$A$6&'S'!$B$1:$B$6,0),MATCH(" & n & "1 & " & n & "2 ,'S'!$A$1:$f$1&'S'!$a$2:$f$2,0))"


Next iRow
Next iCol


End Sub

I just put it out there so someone like me who is trying to loop within excel for array formulas for either rows or columns or both can understand it easily, Would not have figured so quickly without your help so Thank You again.