return row value in another sheet using match or vlookup

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Jul 25, 2004
Messages
5,458
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?
 

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.
 
Joined
Jul 25, 2004
Messages
5,458
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top