Solved

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

Posted on 2011-03-10
11
211 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now