We have a lookup table on my Control WS that looks like attached. I want to write code to do a find on my Object lookup table so that when I pass an ObjSubIn = 8124 then my function will pass back the value of "Q" in column N. How do I do this. This is the code I have coded so far but it is not exactly what I want. Does anyone have any recommendations to cange this code so I get what I want. The client has many different object accounts so I am using this lookup table to identify which column to update in a worksheet instead of hardcoding all my object accounts and columns. Here is the code I have so far. The FIND command works good but I need to return the row and column of where my 8124 was found so I can go two columns over and return the appropriate "Object Column" value.
Function CheckForOBjSubColumn(ObjSubIn As Variant) As String
' Find object/Sub in Control WS and get column where object is to be stored.
' Return Object Column otherwise return "??" to indicate object not found.
Dim ControlCol As Range
Dim ControlCell As Range
Dim ColumnValue As String
WorkCostCenter = Range("Invoice_Cost_Center").Value
Call setSheetNames
Set ControlCol = ControlSheet.Range("$L:$L")
Set ControlCol = ControlCol.Find(ObjSubIn, LookIn:=xlValues, lookat:=xlWhole)
If Not ControlCol Is Nothing Then
ColumnValue = ControlSheet.Range(ControlCol + 2 & ControlCell).Value ' OBJECT/SUB Spreadsheet column
Else
ColumnValue = "??" ' OBJECT/SUB Spreadsheet column not found
End If
CheckForOBjSubColumn = ColumnValue
End Function
Bookmarks