VBA pulling data from table

Question

I have a large table that I've defined as "WW60_STRI" in the name manager of excel

I'm trying to write a function that returns the max number in column 1 of the table. Is below the correct syntax to use because I'm getting #value! as result when typing =wlookup(WW60_STRI,"maxdepth") into a cell

Public Function wlookup(Name As String, Retrieve As String)
Set WW60_STRI = Range("WW60_STRI")
Select Case Name
        Case WW60_STRI:
            Select Case Retrieve
                Case "maxdepth"
                    wlookup = Application.WorksheetFunction.Max(WW60_STRI.Columns(1))
            End Select
        Case Else
            wlookup = "error"
        End Select
End Function

Thanks!

TinMan · Accepted Answer · 2022-08-16 23:25:50Z

1

WW60_STRI needs to be enclosed in quotes.

Public Function wlookup(Name As String, Retrieve As String)
    Set WW60_STRI = Range("WW60_STRI")
    Select Case Name
        Case "WW60_STRI":
            Select Case Retrieve
                Case "maxdepth"
                    wlookup = Application.WorksheetFunction.Max(WW60_STRI.Columns(1))
            End Select
        Case Else
            wlookup = "error"
    End Select
End Function

answered Aug 16, 2022 at 23:25

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

goblinmode Over a year ago

thanks. looks like I should be using quotes in the cell also: =wlookup("WW60_STRI","maxdepth")

Collectives™ on Stack Overflow

VBA pulling data from table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related