Bringing an index/match function over to vba

Hey everybody,

I am currently trying to make an auto populating form using index/match and VBA. The form will function by referencing 2 sheets to populate 1 in the same workbook. A reference name will come from the 'acct settle' sheet and various fields will be pulled from 'synd data' sheet to fill 'wire form' throughout a loop(hense VBA).

My working function in cell is =INDEX('Synd Data'!I$2$:I$20$,MATCH('acct settle'!A6,'Synd Data'!B2:B5,0))

Now my problem here is transferring this function into VBA terms. Here is my best attempt

WorksheetFunction.Index(Worksheets("Synd Data").Range("I$2$:I$20$"), _
WorksheetFunction.Match(Worksheets("Acct settle").Range("B15"), _
Worksheets("Synd Data").Range("B$2$:B$5$"), 0))

I know this is jumping around all over the place between sheets and I think that is the reason for my 1004 run time error. I have also attempted using just Application.Match and Application.worksheetFunction.Match. So my big question is if this is even possible and how I could get this to return a value to a cell regardless of a string or int. Thanks for any help in advance, I know this is a hot mess.

CK76,

Here is the current book I am working on, the first sheet contains all the fields I will need to be pulling through the VBA loop.

Hmm, so what field in "Wire Form" sheet should be manually updated to fill the rest?

Or if it's not "Wire Form" sheet, then what other field controls what to look up?

The wire form will have a couple of manual entry cells, but the information being populated on the sheet is taken from 'Synd Data' (actual data to be put on sheet) and 'Acct settle' (used to indicated which data from 'Synd Data' to pull)

That's the confusing part for me. Shouldn't all controls reside in "Wire Form"?

With current formula set up I see that 'acct settle'!A6 is used as Lookup Value. However, need to understand what logic is used to pick that as LookUp value over others.
Otherwise, code would not be dynamic.

Ideally, the code would check in cell 'acct settle'!A6 to see which bank is being referenced. Then it would check the 'Synd Data' sheet to pull things like acct # and ABA# which would be the values that populate the 'wire form'. From there it would save, print and loop down to cell A7 and so on until the A column displays an empty cell. And I am comfortable with that part, but for some reason the index/match function is giving me a lot of trouble.

Typically I don't use INDEX/MATCH worksheet function within VBA.

I'd do it like below. See if this works for you.

Please Login or Register  to view this content.

You are a life saver, worked like a charm! I have not seen this method used before, very interesting. I was thinking about it earlier today and landed on the same conclusion that index/match wasn't the best option to try to transfer over to VBA but didn't know any alternatives. Thanks again!