Using a variable in index and match in VBA

[gtopmafia]

Can someone please help me fix my formula, Im trying to make it dynamic by using two variables "loanNumber" and "Name". All the other parts are named Cells / ranges.


Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Select
ActiveCell.Formula = _
"=INDEX(Loan_inv_sec,MATCH(loanNumber,Loan_num,0),MATCH(Name,loan_inv_titles,0))"
Range("AN9").Select

 

[steve the fish]

You have to concatenate them into the string:

ActiveCell.Formula = "=INDEX(Loan_inv_sec,MATCH(" & loanNumber & ",Loan_num,0),MATCH(" & Name & ",loan_inv_titles,0))"

PS. I wouldnt use Name as the name of a variable.

 

[gtopmafia]

ok thank you so much!

 

[gtopmafia]

@steve the fish
How can I make the variables or formula include "x" for looking up columns or rows with that title.

 

[gtopmafia]

@steve the fish
How can I make the variables or formula include "x" for looking up columns or rows with that title.

 

[steve the fish]

Sorry not clear what you mean. Your formula looks up rows and columns based on the two match formulas.