How to make a macro vlookup dynamic?

[psrs0810]

How can I take sections table_array & col_index_num of the vlookup and make them dynamic?

every time I run the macro I need to change the table array and the column index. Is there a way I can use a input box, or something so that I can change it when needed?

also, if the table array for the current month is $C:$Q with the column index being the last column = 15. if the next month is column R with an index column of 16, can the input box use the same reference to make that change?

 

[Matty]

Is this just a little part of a bigger macro?

If not, why not just use Data Validation as the month "chooser"? This would keep things tight, as an input box is open to whatever the user wants to put in there, e.g. Jul, July 7, etc., which could cause issues...

Matty

.

 

[psrs0810]

I only have 12 options for the input box - Jan OP, Feb OP, Mar OP, Apr OP, May OP, Jun OP, Jul OP, Aug OP, Sep OP, Oct OP, Nov OP, Dec OP

 

[Matty]

Is the macro really necessary? I don't see that it is from what you have posted so far.

Data Validation would fit the bill as the "chooser". The formula would remain constant, but linked to the Data Validation Cell, hence the results would change if the Data Validation Cell changed...

Matty

 

[psrs0810]

I am trying to utilize this in a currrent project and so that this general format can be used in other macros. that is why I am trying to problem solve this section

 

[Matty]

OK. I'm afraid I'd be doing you a disservice if I provided any code, as I'm still learning VBA myself.

Perhaps one of other folks on here will be able to help you. I have noticed one particularly good coder posting this evening who might be able to help...

Matty

 

[psrs0810]

Ok I have gotten this far by piecing codes together but I am not able to get the index match to work properly. can anyone help?

Dim ColHead As String
Dim ColHeadCell As Range

ColHead = InputBox("Enter Column Heading", "Identify Column", [d1].Value)
If ColHead = "" Then Exit Sub
Set ColHeadCell = Workbooks("Budget by Trans Code.xls").Sheets("OP").Rows(1).Find(ColHead, lookat:=xlWhole)
If ColHeadCell Is Nothing Then
MsgBox "Heading not found in row 1"
GoTo Again
End If
SOMECELL.Value = "=INDEX('[Budget by Trans Code.xls]OP'!" & ColHead & _
",MATCH(A:A,'[Budget by Trans Code.xls]OP'!$C:$C,0))"

 

[Geoff Taylor]

Your code doesn't define SOMECELL. Let's say that the SOMECELL is A1 The code would be

Range("a1").FormulaR1C1 = "=INDEX('[Budget by Trans Code.xls]OP'!" & ColHead & _
",MATCH(A:A,'[Budget by Trans Code.xls]OP'!$C:$C,0))"

However, there is a much simpler way. Have an additional column in your table. Have your conditional dropdown value become the column heading and then do a HLOOKUP from the heading each row. Then your current month becomes a static point that all your other references can link to.

 

[psrs0810]

I changed SOMECELL but I am still getting an error. In the Cell the formula is showing up with the title and not the Column for pulling the data.
So if my title is Apr OP, in column O, I am getting =INDEX('[Budget by Trans Code.xls]OP'!Apr OP,MATCH(A:A,'[Budget by Trans Code.xls]OP'!$C:$C,0))

When it needs to be $O:$O

 

[psrs0810]

can anyone assist? Thank you