Stack Exchange
Offset Height Parameter in VBA
I'm trying to convert an Excel formula into VBA and I'm having some difficulties with the Offset conversion. The formula is being used to perform a VLookup on filtered data.
The Excel formula is:
=VLOOKUP(G4 & "",IF(SUBTOTAL(3,OFFSET(D2:D36419,ROW(D2:D36419)-ROW(D2),0,1))>0,D2:E36419),2,0)
My current VBA code is:
count = Application.WorksheetFunction.VLookup(key & "", _
    IIf(Application.WorksheetFunction.Subtotal(3, _
    ws.Range("D2:D36419").Offset(ws.Range("D2:D36419").Row - ws.Range("D2").Row, 0)) > 0, ws.Range("D2:E36419"), 0), 2, 0)

I need some way to include the Offset height parameter (1). Any ideas?
Note: I've tried
count = Application.WorksheetFunction.VLookup(key & "", _
    IIf(Application.WorksheetFunction.Subtotal(3, _
    ws.Range("D2:D36419").Offset(ws.Range("D2:D36419").Row - ws.Range("D2").Row, 0).Resize(1)) > 0, ws.Range("D2:E36419"), 0), 2, 0)

without success.

There's more than likely a different way to do it in VBA without the use of formula - something likes .SpecialCells(xlCellTypeVisible).  What is the formula meant to do?

I have a large table that I will filter with user data (device and facility are the two filter options). The formula allows me to perform a VLookup on the full range of data but will just look at the visible, filtered cells.

Try something like this:
Public Sub Test()

    Dim SearchRange As Range
    Dim FindValue As Range
    Dim ReturnValue As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
    
        'Note: I've set this one row above your search range so D2 is first cell that's looked at.
        Set SearchRange = .Range("D1:D36419").SpecialCells(xlCellTypeVisible)
        
        Set FindValue = SearchRange.Find(What:=.Range("G4"), After:=SearchRange.Cells(1), _
                                         LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
                                         
        If Not FindValue Is Nothing Then
            Set ReturnValue = FindValue.Offset(, 1)
            MsgBox ReturnValue & " found in " & ReturnValue.Address, vbOKOnly + vbInformation
        Else
            MsgBox "Nothing found.", vbOKOnly + vbInformation
        End If
    End With

End Sub


I'm going to say this is the answer because SpecialCells(xlCellTypeVisible) is giving me the table I need to perform my searches. Thanks!

I sometimes find it hard finding the 'correct' VBA alternative to some of the Excel functions. And resort to this technique range("C1").value="=A1+B1" where you put the Excel formula in speech marks.
Sometimes I would need to know the value of A1+B1 for an if statement, but wouldn't need the total of A1+B1 being displayed. So I may do Range("AAA1481").value = "=A1+B1". It is unlikely AAA1481 will have any actual data in it. It should be out of sight and mind.
And then run a if statement such as -
If Range("AAA1481").value > 1000 then
msgbox ("above 1000")
end if 
Range("AAA1481").clearcontents

Finding the correct VBA way of writing A1+B1 probably isn't too hard but I find my technique is a godsend when dealing with messy formulas like -
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MAX(NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A:A,"L",".4"),"M",".3"),"S",".2"),"R",".1"))),".4","L"),".3","M"),".2","S"),".1","R")


The subtotal function is just being used to determine if a cell is visible or not. I don't think I actually want the summed value.
