VBA Index/Match with dynamic ranges

leatherhen99

New Member
Joined
Dec 17, 2019
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Good Friday All,

I've learned so much for you all, but maybe I don't know how to search for my question to get the answer... Here's what I'm doing... creating a pivottable with data... then I'm creating a table on another sheet to index/match only the names that I need. Here's the formula for the 1st agent and 1st category:
Excel Formula:
=IFERROR(INDEX(Sheet1!$B$5:$CA$180,MATCH($B3,Sheet1!$A$5:$A$180,0),MATCH(C$2,Sheet1!$B$4:$CA$4,0)),"")

VBA Code:
Dim wb4 As Workbook        
Dim PSheet As Worksheet
        Dim DSheet As Worksheet
        Dim PCache As PivotCache
        Dim PTable As PivotTable
        Dim PRange As Range
        Dim LastRow As Long
        Dim LastCol As Long
        Dim ARange As Range
        Dim ColRange As Range
        Dim i As Long
        Dim c As Long

Set wb4 = ActiveWorkbook
'Pivottable is on PSheet

'get index array
        LastRow = PSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = PSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = PSheet.Cells(4, 3).Resize(LastRow, LastCol)
        

'paste headers from pivottable into table matrix
        Range("C3").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Copy
        Sheets("Flex").Select
        Range("B2").PasteSpecial
        Range("B3").Select
'get ARange to match array agents
'get ColRange to match array column headers 
        i = PSheet.Cells(Rows.Count, 1).End(xlUp).Row
        c = PSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set ARange = PSheet.Cells(4, 3).Resize(i, 0)
        Set ColRange = PSheet.Cells(3, 3).Resize(0, c)

I cannot get the formula to work utilizing the ranges (which may not be correct either...). I have tried to use the Immediate window, but I'm not well versed in using that to determine ranges...so my struggle is real! I have deleted all my previous attempts at the formula, so I don't even have that as an example... sorry!

Any help would be greatly appreciated!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,141,581
Messages
5,707,196
Members
421,497
Latest member
philipg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top