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.

 

[RoryA]

Add lValue - 10 to the FoundRow1 value, or use a range object and refer to the range relative to that.

 

[Snakehips]

@sparky2205 I was thinking along these lines???
But I'm unsure about negating your + 3 of FoundValue1 ??

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

 

[RoryA]

If you use a range you don't need to calculate any offsets:

Rng.Cells(FoundRow1).Entirerow.hidden = False

 

[sparky2205]

Hi guys,
I don't think I'm implementing your suggestions in the right way.
I've included the full code below. It's not working.
You will see some commented out code related to my other attempts.

Sub Filter_By_Personnel()

Dim lastrow As Long
Dim count As Long
Dim count1 As Long
Dim lvalue As Long
Dim lvalue1 As Long
Dim personnel As Variant
Dim srange As Long
Dim srange1 As Long
Dim i As Long
Dim i1 As Long


' Prevent screen flicker while processing
Application.ScreenUpdating = False
    srange = 3
    srange1 = 3
    lastrow = Cells(Rows.count, 1).End(xlUp).Row
    ' Hide all vendors
    Rows("3:" & lastrow).EntireRow.Hidden = True
per:    personnel = Application.InputBox("By which personnel do you wish to filter?", Type:=2)
    ' If the user clicks on OK but doesn't enter a personnel
        If personnel = "" Then
            MsgBox ("You have not entered a personnel")
            GoTo per
        ' If the user presses cancel
        ElseIf personnel = False Then
            Call Unfilter_Rows
        Exit Sub
        End If
            
    count = WorksheetFunction.CountIf(Range("B3:B" & lastrow), personnel)
    If count = 0 Then
        MsgBox "Invalid personnel"
        ' Return user to personnel entry box
        GoTo per
    End If
                    
    Dim FoundRow As Long
    Dim FoundRow1 As Long
    Dim RowNum As Long
    Dim strRowsToHide As String
    Dim rngVisible As Range
    Dim rng As Range
    
    For i = 1 To count
        With Application
            ' Find the next occurrence of the personnel using the starting range (srange) and the last found occurrence (FoundRow + lvalue)
            FoundRow = .IfError(.Match(personnel, Range("B" & srange & ":B" & lastrow), 0), 0)
        End With
        If FoundRow = 0 Then Exit For
        lvalue = FoundRow + srange - 1

        If rngVisible Is Nothing Then
            Set rngVisible = Rows(lvalue)
        
        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)
            rng.Cells(FoundRow1).EntireRow.Hidden = False
        End With
        
'        For i1 = 1 To 9
'        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)
'            If FoundRow1 - 9 + i1 = i1 Then
'            'FoundRow1 = .IfError(.Match("*-*", Range("A" & srange1 & ":A" & lastrow), 0), 0)
'            Rows(FoundRow1 + 3).Hidden = False
'            Exit For
'            End If
'        End With
'        Next i1
        If FoundRow1 = 0 Then Exit For
        lvalue1 = FoundRow1 + srange1 - 1

        Else
            ' Add the selected personnel to the range
            Set rngVisible = Union(rngVisible, Rows(lvalue))
                
        For i1 = 1 To 9
        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)
            If FoundRow1 - 9 + i1 = i1 Then
            'FoundRow1 = .IfError(.Match("*-*", Range("A" & srange1 & ":A" & lastrow), 0), 0)
            Rows(FoundRow1 + 2 + i1).Hidden = False
            Exit For
            End If
        End With
        Next i1
        If FoundRow1 = 0 Then Exit For
        lvalue1 = FoundRow1 + srange1 - 1
        
        End If
        srange = lvalue + 1
        srange1 = lvalue1 + 1
    Next i
    
    ' Show all occurrences of the selected personnel
    Dim areaVisible As Range
    For Each areaVisible In rngVisible.Areas
        areaVisible.EntireRow.Hidden = False
    Next areaVisible
    
    Application.ScreenUpdating = True

End Sub

Below is a screenshot of the worksheet. If you need the complete file let me know and I'll post a link.

What I want to do is filter by Employee and have their projects display. In the example below filtering on Emp3 would display:

It would ignore any projects for which there is no line for Emp3.

 

[Alex Blakenburg]

It might be simpler and faster if you replace from "Dim FoundRow As Long" to the end with something like the 2nd lot of code below:
(also your last row line won't work if there are hidden rows for a quick fix add the line in blue before the last row line)

    ActiveSheet.UsedRange.EntireRow.Hidden = False
    lastrow = Cells(Rows.count, 1).End(xlUp).Row

'---------------------------------------------------------------------
Dim rng As Range
Dim arr As Variant
Dim rngVisible As Range

Set rng = Range("A1", Cells(lastrow, "B"))
arr = rng.Value

For i = 1 To UBound(arr)
    If InStr(1, arr(i, 1), "-") Or StrComp(arr(i, 2), personnel, vbTextCompare) = 0 Then
        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 personnel
Dim areaVisible As Range
For Each areaVisible In rngVisible.Areas
    areaVisible.EntireRow.Hidden = False
Next areaVisible

Application.ScreenUpdating = True
End Sub

 

[sparky2205]

Hello again Alex and thanks for the response.
That code above is much neater.
There's just one issue with it. It returns all rows where personnel is found and all rows containing "-" (Project Header Row).
What I want is all rows where personnel is found and all rows containing "-" where personnel is assigned to that Project.
e.g. in my previous screenshot if I were to select Emp5, Row 12 would be visible but not Row 3 or Row 21 as Emp5 is not assigned to those projects.

 

[Alex Blakenburg]

I think you mean that you don't want the section headings if there is nothing in that section that meets the criteria.
This should do that:

'---------------------------------------------------------------------
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), personnel, 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 personnel
Dim areaVisible As Range
For Each areaVisible In rngVisible.Areas
    areaVisible.EntireRow.Hidden = False
Next areaVisible

Application.ScreenUpdating = True
    
End Sub

 

[sparky2205]

Alex,
firstly, let me humbly apologise for my late response. I was off for a couple of weeks without access to my files.
Your solution works perfectly. Thanks very much for your help.

 

[Alex Blakenburg]

Thanks for coming back and closing this out. Glad we could help.