VBA Index Match (relative ref, named range)

[Silverjman]

How would I use/apply the same VBA code across a worksheet to perform an IndexMatch whose inputs would be populated by it's relative location on a worksheet.

i.e., Take the text on ?ActiveCell? row of the named range in column Z ?is a VBA INDIRECT necessary? and then ( , 1 or 2 or 3 depending on the date/relative position of the cell on the ActiveSheet for the range, that is is the 1st or 40th column in a named range on another worksheet

Asset Name on ?ActiveCell? row on same worksheet in column C


The goal here is to use this VBA code as a BeforeDoubleClick event to let the user drilldown to the source of the numbers, because, given the need for speed I've placed IFs in front of the respective SUMIFS.


I realise that this is a poorly structured question, but I've been googling it for awhile now and can't find a way to tighten it up.

 

[baitmaster]

I'm struggling to understand the question, but I think you're missing some knowledge around how you can refer to cells / ranges of cells, and that this will answer your question

You can refer to the first cell on a worksheet as either range("A1") or cells(1,1)
You can refer to the cell a few below it as range("A3") or cells(3,1)

You can also refer to it as range("A2").range("A2") or as range("A2").cells(2,1) or as cells(2,1).range("A2"). All of these are the same thing. You are referring to a range relative to a specific starting point, where that starting point can change

Likewise, if you have a named range you can refer to that as your new basis. So as I understand your question, if you know the row and column number of your start cell, you can simply use these locations against your named range - is that right? If your cell is at location X,Y of a worksheet, you want to find corresponding location X,Y within a named range?

For example,

activecell = Range("myName").cells(activecell.row, activecell.column)

If I'm on the wrong track then let me know

 

[Silverjman]

Ugg type an enormous reply and then hit "Reply to Thread" instead of "Post"

Basically the mirror referencing you suggest won't work because I am doing a sumifs of 2000 rows with the resulting 40 lines of 40 different assets totalling the same Value as the total of the 2000 lines (units within the asset)

So I thought I would try to do a BeforeDoubleClick IndexMatch to let the user drilldown to the source if they didn't agree with the Value (IF,SUMIFS) on the Summary page. But I don't want to write 40 assets X 22 Quarters = 880 snippets of code; but only on snippet and apply it to the lot...

I assumed that to do this I would need to use INDIRECT in the VBA code or do something special.

 

[baitmaster]

Nah I'm still a bit lost with your requirement. I'm gonna struggle to help as I'm confused

I've never had to try and use INDIRECT in a piece of code anywhere so I would suspect that's the wrong approach

I've frequently had to use INDEX and MATCH though, and you can refer to these as methods of the WORKSHEETFUNCTION object, where they have the same structure as the Excel formula, i.e. worksheetfunction.index(range, row#, column#). Perhaps you can make use of this?

You can also make use of the audit arrows and the FIND function to get ranges / source values from your formulas for use in a generic Index/Match?

 

[Silverjman]

Great thanks!

I think this "audit arrows and the FIND function to get ranges / source values from your formulas for use in a generic Index/Match" will help a lot with my future googling!

 

[Silverjman]

and this!

"worksheetfunction.index", never heard of it!

 

[baitmaster]

In VBA type "worksheetfunction." and you'll see a heap of standard Excel formulas appear in a pop-up. You can refer directly to these formulas in VBA - very useful

Any value that feeds into a formula, and is on the same worksheet, can be traced using the blue audit arrows. Selection.DirectPrecedents identifies these. Values on another worksheet are more complex, and a code extract from one of my macros contains a few features that you'd steal to do this

    lngLink = 0
    cl.ShowPrecedents
    
    On Error GoTo errHandle
    Do
        lngLink = lngLink + 1
        If lngLink > 50 Then GoTo nextcl ' escape in case of terminal loop
        
        Set clPrecedent = cl.NavigateArrow(True, 1, lngLink)
        If clPrecedent.Parent.Name <> cl.Parent.Name Then

            ' blah blah blah

        End If
    Loop
    On Error GoTo 0

This useful function tells you the last used row of any worksheet (not the end of the usedrange, which can report incorrect values), and demonstrates Find

Function lastUsedRow(ws As Worksheet) As Long

On Error Resume Next
    lastUsedRow = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
On Error GoTo 0

End Function

 

[Silverjman]

This is the gist of what I want to do, but instead of filter just select the cells!!!!!!!!!!
http://www.mrexcel.com/forum/excel-questions/616156-drilling-down-into-sumifs-formula.html

Step 1: Put the following code behind all sheets with the SUMIFs formulas.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
FilterBySUMIFs Target.Cells(1)
End Sub

Please note that it will only work with a plain SUMIFs formula. If your formula has anything other than a standard sumifs formula, it may need to be modified.

Step 2: Paste the code below into a standard VBA module.

Option Explicit



Sub FilterBySUMIFs(r As Range)

Dim v, ctr As Integer
Dim intField As Integer, intPos As Integer
Dim strCrit As String
Dim rngCritRange1 As Range, rngSUM As Range
Dim wksDataSheet As Worksheet

If Not r.Formula Like "*SUMIFS(*" Then Exit Sub

'split formula by comma, strip the right paren
v = Split(Left(r.Formula, Len(r.Formula) - 1), ",")

'the first criteria range is the 2nd element of the array
Set rngCritRange1 = Range(v(LBound(v) + 1))

'use first criteria range to get a reference to the data sheet
With rngCritRange1
Set wksDataSheet = Workbooks(.Parent.Parent.Name).Worksheets(.Parent.Name)
End With

'clear any existing filter, turn filtering on if needed
With wksDataSheet
If .AutoFilterMode And .FilterMode Then
'clear existing autofilter
.ShowAllData
ElseIf Not .AutoFilterMode Then
'display autofilter arrows
rngCritRange1.CurrentRegion.AutoFilter
End If
End With

'set the filters
For ctr = LBound(v) + 1 To UBound(v)
If ctr Mod 2 <> 0 Then
With wksDataSheet
'determine field in case table does not start in column A
intField = .Range(v(ctr)).Column - .AutoFilter.Range.Columns(1).Column + 1
'use evaluate instead of range(v(ctr + 1))
'so both cell-reference and hard-coded criteria are handled.
strCrit = Evaluate(v(ctr + 1))
.Range(v(ctr)).AutoFilter Field:=intField, Criteria1:=strCrit
End With
End If
Next


'strip left paren and everything to left of it, get the sum range from first element of array
intPos = InStr(1, v(LBound(v)), "(")
Set rngSUM = Range(Replace(v(LBound(v)), Left(v(LBound(v)), intPos), ""))
'select the SUM range so total displays in status bar
Application.Goto rngSUM


End Sub

I'm going to keep working on it an will post the completed code once I figure it out, the above connect a lot of the dots.