Paolo Crossi
asked on
VBA and Excel: how to fill a table matching rows and columns?
Hello everybody.
I have to fill the area C2:E11 - which actually is a table - with the data in the column L. The criteria of filling are set by the columns I:K.
As you can see in the attachment, the value in cell L2 (7) has to be reported in date 29/9/18 related to the pair BBBBB (column j) and XXX (column K) ---> cell D6
And so on for any of the value in column L until L6.
How can I figure it out?
Tabel1.xlsx
I have to fill the area C2:E11 - which actually is a table - with the data in the column L. The criteria of filling are set by the columns I:K.
As you can see in the attachment, the value in cell L2 (7) has to be reported in date 29/9/18 related to the pair BBBBB (column j) and XXX (column K) ---> cell D6
And so on for any of the value in column L until L6.
How can I figure it out?
Tabel1.xlsx
ste5an
hmm, by posting that attachment?
ASKER
Posted.
I have the impression if the author modifies the original question, the attachment disappears.
Anyway, now it should be at the right place.
I have the impression if the author modifies the original question, the attachment disappears.
Anyway, now it should be at the right place.
Try this...
In C2
Tabel1.xlsx
In C2
=IFERROR(INDEX($L$2:$L$6,MATCH(1,INDEX(($J$2:$J$6=$A2)*($K$2:$K$6=$B2)*($I$2:$I$6=C$1),),0)),"")Tabel1.xlsx
ASKER
Fine job.
But it is my second best.
My first choice is a vba code.
But it is my second best.
My first choice is a vba code.
If you don't want to place the formulas manually, you can use VBA to place the formulas and convert them into values.
In the attached, click the button called "Fill Table" to run the code.
In the attached, click the button called "Fill Table" to run the code.
Sub FillTable()
Dim ws As Worksheet
Set ws = Sheets("Foglio1")
With ws.Range("C2:E11")
.Formula = "=IFERROR(INDEX($L$2:$L$6,MATCH(1,INDEX(($J$2:$J$6=$A2)*($K$2:$K$6=$B2)*($I$2:$I$6=C$1),),0)),"""")"
.Value = .Value
End With
End SubASKER
Well.
But in real life, I have variables to manage.
The range to fill is:
The range to take data is:
How can I adapt your solution to the different range sizes?
But in real life, I have variables to manage.
The range to fill is:
sheet1.Range(Cells(2, 4), Cells(lr, data))The range to take data is:
sheet2.Range("AI2:AL" & lr2)How can I adapt your solution to the different range sizes?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.