asked on

looking for data in other worksheets in same workbook

excel 2003 vba code needed( if possible)

What I have:
"Sheet1" with column headers.
"Sheet2" Contains Data
"Sheet3" Contains Data

Sheet 2 and 3 contain the same amount of COLUMNS
with headers...ColumnA : E
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

dlmille

Hey, fordraiders! Long time!

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

Dave

Fordraiders

ASKER

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

dlmille

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

Fordraiders

ASKER

dlmille, Yes correct...I'll take alook at the sheet

dlmille

Here's your code. Sorry, I couldn't integrate into your password protected VBA ;)

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

Open in new window

Dave

Fordraiders

ASKER

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

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

Great Thanks !

Fordraiders

ASKER

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

Fordraiders

ASKER

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.
Can u please help !?

Thanks
fordraiders