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

x
Solved

looking for data in other worksheets in same workbook

Posted on 2012-03-16
Medium Priority
250 Views
excel 2003 vba code needed( if possible)

What I have:
"Sheet2"  Contains Data
"Sheet3" Contains Data

Sheet 2 and 3 contain the same amount of COLUMNS
Column A on both Sheet 2 and 3 will contain the lookup value posted on Sheet1 in ColumnA

ColumnA(Sheet1) will contain the data to look for in the other sheets
Column B:E  will be additional data to return to the rows(on sheet1)  if value found in ColumnA in Sheet2 and Sheet3

Thanks
fordraiders
0
Question by:fordraiders
[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
• 6
• 4

LVL 42

Expert Comment

ID: 37731502
Hey, fordraiders!  Long time!

Can you provide a non-sensitive mock-up to make it a bit easier on us to help you?

Dave
0

LVL 3

Author Comment

ID: 37731615
Here is a mock up..

Column A in Sheet1 looks into ColumnA on sheet 2 and 3 and returns  B:E to Sheet1 in B:E

You can test by putting some numbers from Column A IN SHEET 2 OR 3 IN column A on sheet1
EE-Xref-Data-Pull.xls
0

LVL 42

Expert Comment

ID: 37731683
So, you want a macro to populate the data on Sheet1, where the first column in sheet1 matches the first column in sheet2/sheet3.  If the match occurrs, you want to pull column B:E from the sheet2/sheet3 sheets into B:E in sheet1?

Like the attached?

Are you sure you don't just want to use formulas?

Anyway, confirm what I've put there as correct and I can give you VBA that pastes these formulas in then converts to values for all Sheet1 column A.

You've got the ball ;)

Dave
EE-Xref-Data-Pull.xls
0

LVL 3

Author Comment

ID: 37731703
dlmille, Yes correct...I'll take alook at the sheet
0

LVL 42

Expert Comment

ID: 37731733

``````Option Explicit

Sub alignSheet1()
Dim wkb As Workbook
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim wks3 As Worksheet
Dim rng As Range
Dim r As Range
Dim rFind As Range

Set wkb = ActiveWorkbook
Set wks1 = wkb.Worksheets("Sheet1")
Set wks2 = wkb.Worksheets("Sheet3")
Set wks3 = wkb.Worksheets("Sheet4")

Set rng = wks1.Range("A2", wks1.Range("A" & wks1.Rows.Count).End(xlUp))

For Each r In rng
Set rFind = wks2.Range("A:A").Find(what:=r.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
If Not rFind Is Nothing Then
r.Offset(, 1).Resize(1, 4).Value = rFind.Offset(, 1).Resize(1, 4).Value 'copy B:E values
Else
Set rFind = wks3.Range("A:A").Find(what:=r.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
If Not rFind Is Nothing Then
r.Offset(, 1).Resize(1, 4).Value = rFind.Offset(, 1).Resize(1, 4).Value 'copy B:E values
Else
r.Offset(, 1).Resize(1, 4).Value = vbNullString
End If
End If

Next r
End Sub
``````

Dave
0

LVL 3

Author Comment

ID: 37733272
sorry about that, I thought I cleared it...
dlmille-EE-Xref-Data-Pull.xls
0

LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 37733289
Ok - the app looks through column A and if match is found in the first or second sheet then it updates, otherwise it clears.

Dave
dlmille-EE-Xref-Data-Pull-r1.xls
0

LVL 3

Author Closing Comment

ID: 37767717
Great Thanks !
0

LVL 3

Author Comment

ID: 39471790
dimile, I have a question out like this one...but need to find...additiona matches if they exist.
0

LVL 3

Author Comment

ID: 39481142
dimile, I hope you can help me.
I have a question re-posted on this same type of code.
Redmondd(Brian) has tried to help.
But does not fully undestand what I'm looking for in my opinion.

Thanks
fordraiders
0

Featured Post

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
Suggested Courses
Course of the Month9 days, 10 hours left to enroll