Index match formula to VBA Macro

[norman_bunch]

Hello Board,

I am not very up to speed on using VBA Macro's and was wondering if I could get some help. I have an Index match formula that I would like to be able to use in VBA. I would like for this macro to work on the entire workbook of selected active sheets and populate a column with the values found. Any help I could get would be greatly appreciated. Thanks.

Normally this would be placed in column BX8:BX78.
=INDEX('Cover Sheet'!AA46:AA69,MATCH(BE8&BB8,'Cover Sheet'!D46:D69&'Cover Sheet'!L46:L69,0))

 

[taurean]

Try:

Range("BX8:BX78").Formula = "=INDEX('Cover Sheet'!$AA$46:$AA$69,MATCH(BE8&BB8,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0))"

 

[norman_bunch]

Try:

Range("BX8:BX78").Formula = "=INDEX('Cover Sheet'!$AA$46:$AA$69,MATCH(BE8&BB8,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0))"

Hello Shrivallabha
I have tried the above code and I get #### in the cell. I have tried adding {} to the code and then it just gives me the formula in the cell. If I run the code and then go to that cell and press Ctrl+Shift+Enter it works giving me the value. Would you happen to know what would be causing this?

 

[taurean]

Hello Shrivallabha
I have tried the above code and I get #### in the cell. I have tried adding {} to the code and then it just gives me the formula in the cell. If I run the code and then go to that cell and press Ctrl+Shift+Enter it works giving me the value. Would you happen to know what would be causing this?

Its an ARRAY formula and I missed it:

Range("BX8:BX78").FormulaArray = "=INDEX('Cover Sheet'!$AA$46:$AA$69,MATCH(BE8&BB8,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0))"

 

[norman_bunch]

Its an ARRAY formula and I missed it:

Range("BX8:BX78").FormulaArray = "=INDEX('Cover Sheet'!$AA$46:$AA$69,MATCH(BE8&BB8,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0))"

Thanks Shrivallabha! That seems to do just what I wanted it to do. I was wondering, Is there a way to use this if cells are merged? Example BX1 and BX2, BX3 and BX4, and so on down the column. Also, what would I need to add to the above code to take out the #N/A and leave the columns blank?
Any recommendations would be greatly appreciated. Thanks.

 

[taurean]

Thanks Shrivallabha! That seems to do just what I wanted it to do. I was wondering, Is there a way to use this if cells are merged? Example BX1 and BX2, BX3 and BX4, and so on down the column. Also, what would I need to add to the above code to take out the #N/A and leave the columns blank?
Any recommendations would be greatly appreciated. Thanks.

From what I have read and been reading on forums like these, VBA and Formula get crippled by merged cells. So it is not good practice.

 

[MartinL]

Hi Guys
I have a similar issue. Mine is not an array one though and my code looks like this

IndexNum = Range("b3")
Range("b4").Formula = "Index('Sheet2'!A:A), IndexNum,TRUE)"

but cell B4 always has Index('Sheet2'!$A$2:$A$546), IndexNum,0) and never the result of the formula why is this

 

[VoG]

Try

IndexNum = Range("b3").Value
Range("b4").Formula = "=Index('Sheet2'!A:A)," & IndexNum & ",TRUE)"

 

[MartinL]

Hi Peter

No, that didnt work...
fyi this is my complete code (amended with your code) as it is part of a spinbutton. For some wierd reason it randomly jumps up or down 2,3 or 4 integers. but it did this before your ammendment

Private Sub SpinButton1_SpinDown()
On Error Resume Next
Range("b3").Value = Range("b3").Value - 1
IndexNum = Range("b3").Value
Range("b4").Formula = "=Index(('Sheet2'!A:A)," & IndexNum & ",TRUE)"
End Sub


Private Sub SpinButton1_SpinUp()
On Error Resume Next
Range("b3").Value = Range("b3").Value + 1
IndexNum = Range("b3").Value
Range("b4").Formula = "=Index(('Sheet2'!A:A)," & IndexNum & ", TRUE)"
End Sub

update
=====
after spotting my two typos the code now works apart from the random jumps of increment that are driving me insane

 

[supermiggy]

Its an ARRAY formula and I missed it:

Range("BX8:BX78").FormulaArray = "=INDEX('Cover Sheet'!$AA$46:$AA$69,MATCH(BE8&BB8,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0))"

hi

i know this has been posted a looong time ago..but it is something that i need. where do i actually put the code and run it?

thanks!