VBA function - use range as criteria

Question

I have a VBA function that looks up certain number ID to decide what percentage to use.

I want to make it more dynamic and have a range where the user can enter the ID's so I don't have to manually change it and its static. As you can see in the function onshore.

Function ownershipOffshore(TaxLot)
  If TaxLot = 0 Then
    ownershipOffshore = 0#
  ElseIf TaxLot = Sheets("Ownership %").Range("K26").Value Then
    ownershipOffshore = Sheets("Ownership %").Range("E4").Value
    ' 51506431 Or TaxLot = 51533686 Or TaxLot = 51533687 Or TaxLot = 51533688 Or TaxLot = 51533689 Or TaxLot = 51706542 Or TaxLot = 51864049 Or TaxLot = 51953620 Or TaxLot = 51981353 Or TaxLot = 52142917 Or TaxLot = 52142919 Or TaxLot = 52142920
  ElseIf TaxLot = Sheets("Ownership %").Range("K11:K22").Value Then
    ownershipOffshore = Sheets("Ownership %").Range("E3").Value
  Else
    ownershipOffshore = Sheets("Ownership %").Range("E2").Value
  End If
End Function

Function ownershipOnshore(TaxLot)
  If TaxLot = 0 Then
    ownershipOnshore = 0#
  ElseIf TaxLot = 70376132 Then
    ownershipOnshore = Sheets("Ownership %").Range("K26").Value
  ElseIf TaxLot = 51506431 Or TaxLot = 51533686 Or TaxLot = 51533687 Or TaxLot = 51533688 Or TaxLot = 51533689 Or TaxLot = 51706542 Or TaxLot = 51864049 Or TaxLot = 51953620 Or TaxLot = 51981353 Or TaxLot = 52142917 Or TaxLot = 52142919 Or TaxLot = 52142920 Or TaxLot = 70376132 Then
    ownershipOnshore = Sheets("Ownership %").Range("F3").Value
  Else
    ownershipOnshore = Sheets("Ownership %").Range("F2").Value
  End If
End Function

Answer 1

You can take in a range as a parameter to a function. You can find an example of passing a range into a UDF on my blog.

In your case, you would want to replace this Elif

 ElseIf TaxLot = 51506431 Or TaxLot = 51533686 Or TaxLot = 51533687 Or TaxLot = 51533688 Or TaxLot = 51533689 Or TaxLot =
51706542 Or TaxLot = 51864049 Or TaxLot = 51953620 Or TaxLot =
51981353 Or TaxLot = 52142917 Or TaxLot = 52142919 Or TaxLot =
52142920 Or TaxLot = 70376132 Then

ownershipOnshore = Sheets("Ownership %").Range("F3").Value

 Else

With a boolean function.

 Private Function IsTaxLotInUserRange(byval rng Range, TaxLot)
     Dim cell as Range
     For Each cell in rng
         If TaxLot = cell.Value Then
             IsTaxLotInUserRange = True
             Exit Function
         End If
     Next
End Function

And update your elif with

ElseIf IsTaxLotInRange(rng, TaxLot) Then
    ownershipOnshor = ownershipOnshore = Sheets("Ownership %").Range("F3").Value
Else