Index function within VBA code not producing correct results

alocke

Board Regular
Joined
Nov 10, 2011
Messages
64
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:

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)
if I could somehow change it so that it was
Code:
ROW(k:k)
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
Code:
ROW(1:1) -> Row(2:2)
etc etc.

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

gcz9QQ
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
By the way that i see this, your first formula use k as 0 which anormal. You should put your incrementation (k=k+1) at the start of your loop.

And if you only have 1 row, you don't need to have ROW(k:k), ROW(k) is enough.
 
Upvote 0
By the way that i see this, your first formula use k as 0 which anormal. You should put your incrementation (k=k+1) at the start of your loop.

And if you only have 1 row, you don't need to have ROW(k:k), ROW(k) is enough.
Hi,

If you see the image, resetting k is necessary as if its equals 4 by the time the next iteration occurs (ie when we are looking for "jeffs") it wont run correctly. Resetting it means that we can then run the loop to 1 by the time we are looking for "jeff" and then back to 0 when we are looking for "simon". Also if I change ROW(k:k) to ROW(k) I get an error?

Correct me if I'm wrong but I'm not sure if either these suggestions are the solution to the issue? Regardless I thank you for your answer!

EDIT: What I have noticed as well is that $E$1 remains in the formula throughout. I guess this method of ActiveCell.Formula may not work in this case as it is not dynamic? (ie I can't introduce variables to offset the formula through further iterations).

Not sure why image is not displaying but here is LINK:
Pic1 — imgbb.com
 
Last edited:
Upvote 0
You cannot put Row(k) inside a string literal value if that is supposed to be a formula. Remember that this should be the same as the formula inside a cell (when all is said and done), and as you know if you type =Row(k) into a cell you will get an error.

you have to build out your formula from the variables.

when finished test it by copying the resulting string into a spreadsheet cell to make sure it is a valid formula.

Code:
dim str1 as String, str2 as String, str3 as String
str1 = "ROW(" & k & ":" & k & ")"
str2 = "INDEX(table_all,SMALL(IF(Table_name=$E$1,ROW(table_name))," & str1 & "),2))"
str3 = "=IF(ISERROR(" & str2 & "," & char(34) & char(34) & "," & str2 & ")"
debug.print str3 '//This should work as is


Note:
What I have noticed as well is that $E$1 remains in the formula throughout.
same story. $E$1 is hardcoded into your formula. It doesn't change because of that. Also, using the $x$y notation in a cell address anchors the reference. If you do this, it means (even in a regular spreadsheet formula) that the reference should not change. So if it's meant to be a changing reference in different rows/cells then the anchors are probably misleading at best.


Note 2:
Personally, I think you could just put the formula in the first cell, and copy it to the remaining cells.
 
Last edited:
Upvote 0
xenou,

Awesome! Your advice has worked a charm, I managed to play around and make it work.

I don't know if I necessarily follow your 2nd note though. If we are looking for bob 3 times, then great we will find him 3 times. If I tried the same thing on a list of thousands of entries with some entries not even of interest then copying the formula down would not work would it?
 
Upvote 0
Hi, That's great.
On 2nd note I just assumed the reason for the looping/iteration was to fill down the formula. So you should be good.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top