1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

return row value in another sheet using match or vlookup

Discussion in 'Business Applications' started by spooky1, Nov 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    Need to establish what row company name is in on another sheet using vba with the intention of changing data on the row that the company name is in using

    Sheets("blahblah").Cells(123, 7).Value ="contact name"


    Thank u in anticipation.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    Without knowing anything else, or other specifics, I would recommend either utilizing the EVALUATE() function and use a standard MATCH() function, or you could utilize the Find() method in VBA.

    What range are you looking up? Are there duplicate values in the range? If so, is it sorted, and which value would you want returned?
     
  3. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    Zack, I really wouldn't know how to combine all your suggestion into code.

    I have a list of company names on sheet blahblah in one col and no duplicates. Whilst doing work on other sheets I need to update any missing data related to a company name. All relevant data for each company name is stored on a row. If i can establish what row the company name is on then anything in that row could be added to or amended using this handy code, as an example, Sheets("blahblah").Cells(123, 7).Value ="contact name"

    So all i need is code how to lookup company name in sheet one and establish the row number.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Here is an example of each. All return a number. The third method, however, actually can return a cell range object, although I specified it to return the row (with the ".Row" at the end of the line of code).

    Code:
    Sub TestMethodsForFindingData()
    
        Dim iRow1 As Long
        Dim iRow2 As Long
        Dim iRow3 As Long
        Dim sRetVal As String
        
        Const sCompanyName As String = "Company Name You Want To Find Goes Here"
        
        'Assming data is in Sheet1, column A, of workbook code resides in
        
        '/// Method 1 - Evaluate()
        iRow1 = Evaluate("=MATCH(""" & sCompanyName & """,[" & ThisWorkbook.Name & "]Sheet1!A:A,0)")
        
        '/// Method 2 - MATCH()
        iRow2 = WorksheetFunction.Match(sCompanyName, ThisWorkbook.Sheets("Sheet1").Range("A:A"), 0)
        
        '/// Method 3 - Find()
        iRow3 = ThisWorkbook.Sheets("Sheet1").Range("A:A").Find(What:=sCompanyName, LookIn:=xlValues, LookAt:=xlWhole).Row
        
        sRetVal = "Method 1 returned: " & iRow1 & vbNewLine
        sRetVal = sRetVal & "Method 2 returned: " & iRow2 & vbNewLine
        sRetVal = sRetVal & "Method 3 returned: " & iRow3
        
        MsgBox sRetVal
        
    End Sub
    HTH
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026652

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice