INDEX/MATCH into VBA

[Rymare]

I know this question has been asked here before--many times--but I am still unable to figure this out.
I have a sheet, "Access_Converts", and it has 2 columns.

A: fruit group	B: inumber
Cucumber	123
Banana	56-78
Cucumber	634-2
Apple	2598

<tbody>
</tbody>

And then an MSAccess_AD sheet

A: fruit group	B	C	D	E	F	G	H	I: inumber
	UN	IM	PO	RT	A	N	T	123
	UN	IM	PO	RT	A	N	T	56-78
	UN	IM	PO	RT	A	N	T	634-2
	UN	IM	PO	RT	A	N	T	634-2
	UN	IM	PO	RT	A	N	T	2598
	UN	IM	PO	RT	A	N	T	2598

<tbody>
</tbody>

I need, basically, this (which finds match between Access_Converts column B (inumber), and MSAccess_AD column I(inumber), then returns the value of Access_Converts Column A(fruit group) into column A of MSAccess_AD (fruit group)):

=INDEX(Access_Converts!A:A,MATCH(I2,Access_Converts!B:B,0))

in VBA form.

Reasons I cannot use the formula:

1) My data is too large and always changing. The rows are never a fixed number, data is constantly added, and my range needs to be dynamic.

2) I cannot use the usual, macro/vba "if cell in column I is not blank paste this index/match formula into column A". This data will be uploaded to an access database (an entirely different animal) and I don't want the formula--I want the actual value (i.e. banana, apple etc)

Things I have tried

1) VBA Find, and looping through
Far too many 'for eaches' that didn't make sense, ended up failing multiple times.

2) application.match
The closest I came was with Application.Match, but it stopped on row 1 of MSAccess_AD

3) VBA replace
Pretty much erased a bunch of my data

I don't have this in an Access protocol/table/query/whatever you call it because an individual would have to manually enter the same fruit group for thousands of numbers, whereas this is more automatic and can then be transferred into an access table.

Any help is, as always, much appreciated

 

[BiocideJ]

Can't actually test this code, but I believe this will basically do what you are looking for.

Sub GetIndexMatch()
    With Sheets("MSAccess_AD")


        .Range("A2").Formula = "=INDEX(Access_Converts!A:A,MATCH(I2,Access_Converts!B:B,0))"
        .Range("A2").Copy .Range("A2:A" & .Range("I" & .Rows.Count).End(xlUp).Row)
        .Range("A2:A" & .Range("I" & .Rows.Count).End(xlUp).Row).Calculate
        .Range("A2:A" & .Range("I" & .Rows.Count).End(xlUp).Row).Value = .Range("A2:A" & .Range("I" & .Rows.Count).End(xlUp).Row).Value
    End With
End Sub

MAKE SURE YOU SAVE BEFORE TESTING THIS.

 

[Rymare]

Worked perfectly thank you! I will save this code for the future.