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
<tbody>
</tbody>
Report Example 2
<tbody>
</tbody>
Report Example 3
<tbody>
</tbody>
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!
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
A | B | C | D | E |
Name | INV# | Int | Tax | INV Amt |
Smith, Brad | 1001 | $3.44 | $7.75 | $100.00 |
<tbody>
</tbody>
Report Example 2
A | B | C |
Name | INV# | INV Amt |
Smith, Brad | 1001 | $100.00 |
<tbody>
</tbody>
Report Example 3
A | B | C | D | E | F | G |
Name | INV# | Cred | Exp | Int | Tax | INV Amt |
Smith, Brad | 1001 | $25.00 | $15.00 | $3.44 | $7.75 | $100.00 |
<tbody>
</tbody>
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!