How to find row in 2nd excel SS that matches data in cell of 1st excel SS?

I have an Inspection Checklist (excel form) with info for a particular item.  I want to find that item on a spreadsheet and then copy/paste the updated info from the checklist.   Please help!
SRMoxyAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
See the sample attached. When you click the button on the sheet, it will give you the row from the other workbook.

Sid

Code Used

Private Sub CommandButton1_Click()
    Dim i As Long
    Dim strSearch As String
    Dim aCell As Range
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Expansion Joint Checklist")
    
    '~~> Change Path here
    Set wb2 = Workbooks.Open("C:\SampleExpJoint-SpreadSheet.xls")
    Set ws2 = wb2.Sheets("EXP JOINT LIST")
    
    strSearch = ws1.Range("H5").Value
    
    Set aCell = ws2.Cells.Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        i = aCell.Row
        
        MsgBox "the row that you want is " & i
        
        '~~> Enter the description in sheets EXP JOINT LIST
        'ws2.Range("N" & i).Value = ws1.Range("J6").Value
        '
        ' and so on update the rest
        '
    Else
        MsgBox strSearch & " not found in " & wb2.Name
    End If
End Sub

Open in new window

SampleExpJointChecklist.xls
0
 
royhsiaoCommented:
It looks like you could use vlookup.
would you be able to provide an example?
0
 
SiddharthRoutCommented:
You can use Excel's VBA .Find to look for an item in a SS. If you can provide a sample then maybe I can give you the exact code?

Sid
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
SRMoxyAuthor Commented:
Here's a sample of the checklist & the spreadsheet  - where I'd like to find the correct row, and
then copy/paste data in appropriate cells.    (ie:  the date an inspection was done)
I've been able to copy and paste some info back and forth between the two.  But can't get it to find the row on the spreadsheet with the appropriate ExpansionJjoint ID # that corresponds to the checklist.
Thanks a bunch!
 SampleExpJointChecklist.xls SampleExpJoint-SpreadSheet.xls
0
 
royhsiaoCommented:
you could use activecell.row to find the row.
Sub myRow()

MsgBox Activecell.Row

End Sub

Open in new window

0
 
SiddharthRoutCommented:
Also before you run the macro, chnage the path in the above code for "SampleExpJoint-SpreadSheet.xls"

Sid
0
 
SiddharthRoutCommented:
>>>EXPERTS,
I need your help. Please post your closing recommendations within a few days. If you do not respond, I may need to assume that no correct answer was provided.

Thanks Tracy. The solution that I gave in ID: 35109237 should work. waiting for OP's feedback.

Sid
0
 
SRMoxyAuthor Commented:
Sorry I have been away on other projects -- SiddharthRout -- Thanks very much -- so far I've
been able to get to the row I want, now I'm working on the second half, which was transferring the data to the appropriate cells in that row.    I'll post if it works ASAP.    Thanks again!    
0
 
SRMoxyAuthor Commented:
Thanks so much to everyone for pitching in!   (Thanks to SiddharthRout )I've got it working and transferring the data,  just working the bugs out to make it easy for non-excel users to easily enter and save the info.    Appreciate the help!!!!  
0
 
SiddharthRoutCommented:
You are welcome :)

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.