Go Premium for a chance to win a PS4. Enter to Win

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
?
219 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

916 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