Results 1 to 5 of 5

Want to do a search on one column then return a value from a column two over

Threaded View

cmwilbur Want to do a search on one... 08-13-2010, 11:48 AM
cmwilbur Re: Want to do a search on... 08-13-2010, 02:32 PM
Whizbang Re: Want to do a search on... 08-13-2010, 03:38 PM
cmwilbur Re: Want to do a search on... 08-13-2010, 03:45 PM
Whizbang Re: Want to do a search on... 08-13-2010, 03:56 PM
  1. #1
    Registered User
    Join Date
    06-11-2010
    Location
    Windsor, Canada
    MS-Off Ver
    Excel 2007
    Posts
    55

    Question Want to do a search on one column then return a value from a column two over

    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
    Attached Images Attached Images
    Last edited by cmwilbur; 08-13-2010 at 03:41 PM.
    cmwilbur

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1