Solved

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

Posted on 2011-03-10
11
213 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
  • 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 500 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

776 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