How to create a VLOOKUP in VBA with a variable array/column index dependent on number of columns

[jdk8008]

Hi there,

I was wondering if anyone had any ideas on how to accomplish this.

Currently, I have customer reports that I am creating macros for that analyzes the data on the report. The problem is the format of the report changes depending on which columns the customer wants to display:

Report Example 1
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]INV#[/TD]
[TD="align: center"]Int[/TD]
[TD="align: center"]Tax[/TD]
[TD="align: center"]INV Amt[/TD]
[/TR]
[TR]
[TD]Smith, Brad[/TD]
[TD="align: center"]1001[/TD]
[TD="align: right"]$3.44[/TD]
[TD="align: right"]$7.75[/TD]
[TD="align: right"]$100.00[/TD]
[/TR]
</tbody>[/TABLE]






Report Example 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]INV#[/TD]
[TD="align: center"]INV Amt[/TD]
[/TR]
[TR]
[TD]Smith, Brad[/TD]
[TD="align: center"]1001[/TD]
[TD="align: right"]$100.00[/TD]
[/TR]
</tbody>[/TABLE]

Report Example 3
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]INV#[/TD]
[TD="align: center"]Cred[/TD]
[TD="align: center"]Exp[/TD]
[TD="align: center"]Int[/TD]
[TD="align: center"]Tax[/TD]
[TD="align: center"]INV Amt[/TD]
[/TR]
[TR]
[TD]Smith, Brad[/TD]
[TD="align: center"]1001[/TD]
[TD="align: right"]$25.00[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$3.44[/TD]
[TD="align: right"]$7.75[/TD]
[TD="align: right"]$100.00[/TD]
[/TR]
</tbody>[/TABLE]

The common denominator in all of the reports is that the column headers will always be named the same, but the column may or may not exist on the report. I was thinking maybe we could use header names as a reference point possibly? I don't know how to code this, but this it what would be in general logic:

lets say I want to display sheet1 INV#:
Report 1: IF header = Name, INV#, Int, Tax, INV Amt, then VLOOKUP(B2,'Sheet1'!B:E,1,0)
Report 2: IF header = Name, INV#, INV Amt, then VLOOKUP(B2,'Sheet1'!B:C,1,0)
Report 3: IF header = Name, INV#, Cred, Exp, Int, Tax, INV Amt, then VLOOKUP(B2,'Sheet1'!B:G,1,0)

If column header "X" exists, +1 column in the array of the VLOOKUP formula, if it doesn't -1 column in the array of the formula.

also

The column index could be variable. Lets say I want to display sheet1 tax:

Report 1: IF header = Name, INV#, Int, Tax, INV Amt, then VLOOKUP(B2,'Sheet1'!B:E,3,0)
Report 3: IF header = Name, INV#, Cred, Exp, Int, Tax, INV, then VLOOKUP(B2,'Sheet1'!B:G,4,0)



Here is basis of what I have now for the formula:

Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet1!C[-2]:C[-1],2,0)"

But I am not sure how to make this formula change based on which columns are included on the report. Any help would be great!

 

[RudiS]

How do you want to specify the parameters to locate? Must the macro collect the lookup value from the sheet or are you passing it into the macro via an inputbox?
I would recommend using an INDEX(array,MATCH(...),MATCH(..)) setup that allows for a two directional lookup, vertically for the INV# and horizontally for the appropriate column header.

 

[jdk8008]

How do you want to specify the parameters to locate? Must the macro collect the lookup value from the sheet or are you passing it into the macro via an inputbox?
I would recommend using an INDEX(array,MATCH(...),MATCH(..)) setup that allows for a two directional lookup, vertically for the INV# and horizontally for the appropriate column header.

That sounds much easier than what I was envisioning. I will give it a try. Thanks!