Absolute reference from a lookup

[sparky2205]

Is there a way to return an absolute row number from a lookup given the relative row number within the lookup?
I have a spreadsheet with a lookup (MATCH) in a loop.
Because I am looking for repeating values, each time it loops it resets the starting position of the lookup to the last found value +1.
So, the number returned from the lookup might be 12, i.e. the match was found in position 12 of the lookup range.
But this might be row 54 of the spreadsheet.
Is there a way to turn that relative lookup reference (e.g. 12) into an absolute workbook reference (e.g. 54)?
This is my lookup:

With Application
            ' Find the next occurrence of the personnel using the starting range (srange) and the last found occurrence (FoundRow + lvalue)
            FoundRow1 = .IfError(.Match("*-*", Range(("A" & lvalue - 9) & ":A" & lastrow), 0), 0) + 3
            Rows(FoundRow1).Hidden = False
End With

This is a snippet of the code just to give an example of what I'm doing.
Currently this returns the same row number every time as the relative reference within the lookup always returns the same value to FoundRow1, even though the lvalue value changes.

Hopefully there is enough detail here to explain what I'm looking for. Please ask if more is required.

 

[sparky2205]

Hi Alex,
I'm hope I'm not transgressing by coming back to this post, but it is directly related to it.
I'm trying to create a new macro that will do exactly the same as post #8 but for department instead of personnel.
This is what I've got:

Sub Filter_By_Dept()

Dim lastrow As Long
Dim count As Long
Dim lvalue As Long
Dim dept As Range
'Dim dept1 As String
Dim i As Long

Call Unfilter_Rows

'On Error GoTo errhandler

' Prevent screen flicker while processing
Application.ScreenUpdating = False
    srange = 3
    lastrow = Cells(Rows.count, 1).End(xlUp).Row
    ' Hide all vendors
dep:    Set dept = Application.InputBox("Select a Department from column A", Type:=8)
    'dept1 = Left(dept, InStr(1, dept, " ") - 1)
    Rows("3:" & lastrow).EntireRow.Hidden = True
    ' If the user clicks on OK but doesn't enter a department
        If dept = "" Then
            MsgBox ("You have not entered a department")
            GoTo dep
        ' If the user presses cancel
        ElseIf dept = False Then
            Call Unfilter_Rows
        Exit Sub
        End If
            
    count = WorksheetFunction.CountIf(Range("A3:A" & lastrow), dept)
    If count = 0 Then
        MsgBox "Invalid department"
        ' Return user to department entry box
        GoTo dep
    End If
                    
    Dim rng As Range
    Dim arr As Variant
    Dim rngVisible As Range
    Dim bHasDetail As Boolean
    
    Set rng = Range("A1", Cells(lastrow, "B"))
    arr = rng.Value
    
    For i = UBound(arr) To 1 Step -1
    
        If InStr(1, arr(i, 1), "-") And bHasDetail = True Then
            Set rngVisible = Union(rngVisible, Range("A" & i))
            bHasDetail = False
        End If
    
        If StrComp(arr(i, 2), dept, vbTextCompare) = 0 Then
            bHasDetail = True
            If rngVisible Is Nothing Then
                Set rngVisible = Range("A" & i)
            Else
                Set rngVisible = Union(rngVisible, Range("A" & i))
            End If
        End If
    Next i
    
    ' Show all occurrences of the selected departments
    Dim areaVisible As Range
    For Each areaVisible In rngVisible.Areas
        areaVisible.EntireRow.Hidden = False
    Next areaVisible
    
    Application.ScreenUpdating = True
Exit Sub
    
errhandler:
    Application.ScreenUpdating = True
    Call Unfilter_Rows
    
End Sub

This errors on:

For Each areaVisible In rngVisible.Areas

I believe this is because rngVisible is Nothing.
When tracing this I believe this is because bHasDetail is always False and therefor rngVisible never gets populated.
Being honest I don't understand what bHasDetail is. I can see it's Boolean but I don't understand how it works or how it gets set to True initially.
Maybe this is why I can't figure out how to adjust the code for department.

 

[Alex Blakenburg]

I need some visibility of where to find Department. I can't see it in your original images.
If its in a different spreadsheet with verything the same except the heading of column B being Dept then you technically don't need to change anything although it would be clearer if you did a replace of the variable personnel with a more meaningful name.

In terms of bHasDetail, all that is doing is that
1) the section heading row is identified in column A based on it containing a "-".
2) we are working from the bottom up so when we get to the heading line we need to know if that section had a qualifying detail line
3) so when we find a qualifying detail line set bHasDetail to True so when we get to the parent section heading row we know we need to include it.

 

[sparky2205]

Hi Alex,
department is in column A in the original images. e.g. R&D Project Lead, NPI Project Lead etc.

 

[Alex Blakenburg]

Try changing the 2 lines in blue:

    Set rng = Range("A1", Cells(lastrow, "A"))                      ' Changed to only use Column A
    arr = rng.Value
    
    For i = UBound(arr) To 1 Step -1
    
        If InStr(1, arr(i, 1), "-") And bHasDetail = True Then
            Set rngVisible = Union(rngVisible, Range("A" & i))
            bHasDetail = False
        End If
    
        If StrComp(arr(i, 1), dept, vbTextCompare) = 0 Then         ' Changed to use Column A (chg index from 2 to 1)
            bHasDetail = True
            If rngVisible Is Nothing Then
                Set rngVisible = Range("A" & i)
            Else
                Set rngVisible = Union(rngVisible, Range("A" & i))
            End If
        End If
    Next i

 

[sparky2205]

Yes, that works Alex. Thanks again.
If I might impose on you one last time?
I'd like to be able to select based on the first part of the department, i.e. R&D, NPI, Quality etc.
So if I selected A4: R&D Project Lead, then the result would be R&D Project Lead plus R&D Project Engineer i.e. anything beginning with R&D.
I attempted this by assigning the characters before the space to a variable "dept1" and replacing "dept" with "dept1" where I though appropriate in the code.
I tried using dept1 as both a string and a range variable but with no success.

Sub Filter_By_Dept1()

Dim lastrow As Long
Dim count As Long
Dim lvalue As Long
Dim dept As Range
Dim dept1 As String
Dim i As Long

Call Unfilter_Rows

'On Error GoTo errhandler

' Prevent screen flicker while processing
Application.ScreenUpdating = False
    srange = 3
    lastrow = Cells(Rows.count, 1).End(xlUp).Row
    ' Hide all vendors
dep:    Set dept = Application.InputBox("Select a Department from column A", Type:=8)
    dept1 = Left(dept, InStr(1, dept, " ") - 1)                                            *************** New code to get start of department ******************
    Rows("3:" & lastrow).EntireRow.Hidden = True
    ' If the user clicks on OK but doesn't enter a department
        If dept = "" Then
            MsgBox ("You have not entered a department")
            GoTo dep
        ' If the user presses cancel
        ElseIf dept = False Then
            Call Unfilter_Rows
        Exit Sub
        End If
            
    count = WorksheetFunction.CountIf(Range("A3:A" & lastrow), dept)               ****************** May have to change this to account for dept1 ******************
    If count = 0 Then
        MsgBox "Invalid department"
        ' Return user to department entry box
        GoTo dep
    End If
                    
    Dim rng As Range
    Dim arr As Variant
    Dim rngVisible As Range
    Dim bHasDetail As Boolean
    
    Set rng = Range("A1", Cells(lastrow, "A"))
    arr = rng.Value
    
    For i = UBound(arr) To 1 Step -1
    
        If InStr(1, arr(i, 1), "-") And bHasDetail = True Then
            Set rngVisible = Union(rngVisible, Range("A" & i))
            bHasDetail = False
        End If
    
        If StrComp(arr(i, 1), dept1, vbTextCompare) = 0 Then               ************* Change to dept1 ***************
            bHasDetail = True
            If rngVisible Is Nothing Then
                Set rngVisible = Range("A" & i)
            Else
                Set rngVisible = Union(rngVisible, Range("A" & i))
            End If
        End If
    Next i
    
    ' Show all occurrences of the selected departments
    Dim areaVisible As Range
    For Each areaVisible In rngVisible.Areas
        areaVisible.EntireRow.Hidden = False
    Next areaVisible
    
    Application.ScreenUpdating = True
Exit Sub
    
errhandler:
    Application.ScreenUpdating = True
    Call Unfilter_Rows
    
End Sub

 

[Alex Blakenburg]

Add something like this to the top:

Dim deptPrefixLen As Long

Then change the line in blue

dep:    Set dept = Application.InputBox("Select a Department from column A", Type:=8)
    deptPrefixLen = InStr(1, dept, " ") - 1       ' Get length of prefix / grouping part of dept

' ....... original code here .........

 For i = UBound(arr) To 1 Step -1
    
        If InStr(1, arr(i, 1), "-") And bHasDetail = True Then
            Set rngVisible = Union(rngVisible, Range("A" & i))
            bHasDetail = False
        End If
    
        If StrComp(Left(arr(i, 1), deptPrefixLen), Left(dept.Value, deptPrefixLen), vbTextCompare) = 0 Then  ' Changed to use the prefix part only
            bHasDetail = True
            If rngVisible Is Nothing Then
                Set rngVisible = Range("A" & i)
            Else
                Set rngVisible = Union(rngVisible, Range("A" & i))
            End If
        End If
    Next i

 

[sparky2205]

Hi Alex,
EVERYTHING works perfectly now.
Thanks a million for all your help.

 

[Alex Blakenburg]

No problem. Glad I could help.