Hi,
I have a simplified version of what I want to achieve. Say I have a list containing multiple names (See attached) and I want to post the value associated with that name in a corresponding cell using the formula:
where "table_name" is the table containing all names and "table_all" is the table containing both the names and the values.
The code isn't running correctly and for "bob" it is only displaying the first number 3 times (the image shows it after running the code).
I feel the issue is with
if I could somehow change it so that it was
where k was referring to the integer I declared rather than the Column K, I feel it would work. ie. each time it ran through the loop it would increase, so
etc etc.
Does this make sense?
Here is current code:
I have a simplified version of what I want to achieve. Say I have a list containing multiple names (See attached) and I want to post the value associated with that name in a corresponding cell using the formula:
Code:
=IF(ISERROR(INDEX(table_all,SMALL(IF(Table_name=$E$1,ROW(table_name)),ROW(1:1)),2)),"",INDEX(table_all,SMALL(IF(Table_name=$E$1,ROW(Table_name)),ROW(1:1)),2))
where "table_name" is the table containing all names and "table_all" is the table containing both the names and the values.
The code isn't running correctly and for "bob" it is only displaying the first number 3 times (the image shows it after running the code).
I feel the issue is with
Code:
ROW(1:1)
Code:
ROW(k:k)
Code:
ROW(1:1) -> Row(2:2)
Does this make sense?
Here is current code:
Code:
Private Sub tester()
Dim k As Integer
Dim j As Integer
Dim LR As Long
Range("F1").Select
LR = Range("D" & Rows.Count).End(xlUp).Row
For j = 0 To LR
k = 0
Do Until k = Range("D1").Offset(j, 0):
ActiveCell.Formula = "=IF(ISERROR(INDEX(table_all,SMALL(IF(Table_name=$E$1,ROW(table_name)),ROW(k:k)),2)),"""",INDEX(table_all,SMALL(IF(Table_name=$E$1,ROW(Table_name)),ROW(k:k)),2))"
ActiveCell.Offset(1, 0).Select
k = k + 1
Loop
Next
End Sub