VBA Formula= to populate Rows and Cols of Table

[Daniel342]

I have 20 files I need to link to a summary file. The files all have the same name and format but differ by one folder in where they are located. I could use indirect to link the files, but as we all know the files need to be open for the values to be updated.

I would like to use Index(Filepath-Array, Row, Col) with the following locations.

Filepath: Row 21
Row: Col B
Col: Row 23

Table for Index formula C24:AA40



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]File PathA[/TD]
[TD]File Path B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cols ->[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Rows v[/TD]
[TD]Report[/TD]
[TD]Subject1[/TD]
[TD]Subject2[/TD]
[TD]Subject3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Type1[/TD]
[TD]=INDEX(FilePathA,Row,Col)[/TD]
[TD]=INDEX(FilePathB,Row,Col)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Type2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Type3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Using the following VBA I have created a work around to fit my needs for one column. I cant figure out how to get it to work for all columns in my table. There are currently 20 and more will be added.


Sub Test1()


Application.DisplayAlerts = False

'a is the file path and array to be used INDEX
a = Range("D21").Value


Range("D25:D40").Formula = "=Index(" + a + ",$B25,D$23)"
Application.DisplayAlerts = True


End Sub



Thanks in advance!!

 

[Daniel342]

My previous post was not as simple as it could have been...sooo...

I need a vba code to put the following formula in a table.

=Index(Filepath-Array, Row, Col).

Where Filepath is a string and specific to each columns
And Row and Col are all cell references.
As summary of what I need is below, the final table will be 20+ Rows and Columns

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]File PathA[/TD]
[TD]File Path B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cols[/TD]
[TD]C3[/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD]Rows[/TD]
[TD][/TD]
[TD]Text[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]Text[/TD]
[TD]=INDEX(FilePathA,$A5,C$3)[/TD]
[TD]=INDEX(FilePathB,$A5,D$3)[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]Text[/TD]
[TD]=INDEX(FilePathA,$A6,C$3)[/TD]
[TD]=INDEX(FilePathB,$A6,D$3)[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]Text[/TD]
[TD]=INDEX(FilePathA,$A7,C$3[/TD]
[TD]=INDEX(FilePathB,$A7,D$3)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Using the following VBA I have created a work around to fit my needs for one column. I cant figure out how to get it to work for all columns in my table. There are currently 20 and more will be added.


Sub Test1()
Application.DisplayAlerts = False
'a is the file path and array to be used INDEX
a = Range("D21").Value

Range("D25:D40").Formula = "=Index(" + a + ",$B25,D$23)"
Application.DisplayAlerts = True
End Sub


Thanks in advance!!