Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-10
11
Medium Priority
?
217 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:SRMoxy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
11 Comments
 
LVL 6

Expert Comment

by:royhsiao
ID: 35097662
It looks like you could use vlookup.
would you be able to provide an example?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35097671
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
 

Author Comment

by:SRMoxy
ID: 35099017
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:royhsiao
ID: 35108998
you could use activecell.row to find the row.
Sub myRow()

MsgBox Activecell.Row

End Sub

Open in new window

0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35109237
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35109249
Also before you run the macro, chnage the path in the above code for "SampleExpJoint-SpreadSheet.xls"

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35318136
>>>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
 

Author Comment

by:SRMoxy
ID: 35327087
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
 

Author Comment

by:SRMoxy
ID: 35327580
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35327588
You are welcome :)

Sid
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question