Help needed for FIND Method

[22strider]

Hello Friends

Could you please help me with the following:

I am using "FIND" method for comparing two columns in 2 spreadsheets. So if cell value in sheet1 matches with cell value in sheet2 then copy multiple columns from sheet2 to sheet1 (similar to what VLOOKUP does but I need to return multiple values if the comparison is true).

Values that I comparing are in Column A in both the sheets. Is match I need to return values from about 20 columns (from sheet2 to sheet1). One value that I am returning is in Column B, I was able to get that working. but other values are in columns starting from M. I do not know how to get those values from Sheet2 to Sheet1.

Following is the portion of code that deals with this FIND method

Set wsTools = Worksheets("AllTools")
Set wsToolParts = Worksheets("Tools and Parts")

Set ToolPartsRange = wsToolParts.Range("A2:A" & lR)
Set AllToolRange = wsTools.Range("A2:A" & lR3)

For Each ToolPartsCells In ToolPartsRange

    Set ToolCells = AllToolRange.Find(What:=ToolPartsCells, LookIn:=xlValues, _
                      LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                      MatchCase:=False, SearchFormat:=False)
    
    If Not ToolPartsCells Is Nothing Then
        
        ToolPartsCells.Offset(, 1) = ToolCells.Offset(, 1)
        
    End If
Next

As you may notice that the following line brings next column (B) back but I do not know how to get other column values. They are in columns from M to Z

ToolPartsCells.Offset(, 1) = ToolCells.Offset(, 1)

Thanks for your help
Rajesh

 

[patel45]

It's not clear for me, attac a link to a sample file with current and desired sheets.
this line If Not ToolPartsCells Is Nothing Then is wrong, you hav to change in
If Not ToolCells Is Nothing Then

 

[22strider]

Hello

Please see my previous message for the question, I am attaching sample source and sample expected data:

Excel 2007
	A	B	C	D	E
2			Source1
3	Manufacturer Part Number	Manufacturer Name	Lifecycle Phase	Tool1	Tool2
4	10164	MODERN	Unassigned	Fair
5	512504	ABC INC	Unassigned	Excellent
6	512505	OPTEX INC	Unassigned	Excellent
7	B0001	CORPORATION	Unassigned	Excellent
8	B0002	CORPORATION	Unassigned	Excellent
9	B0003	CORPORATION	Unassigned	Excellent
10	B0004	CORPORATION	Unassigned	Excellent
11	B0005	CORPORATION	Unassigned	Excellent
12	B0011	HONG KONG	Approved	Excellent
Source1

Excel 2007
	A	B	C	D
2		Source2
3	Part Number	Description	Manufacturers.MPN Type	Tool
4	ABCD	Random description	Tool	10164
5	EFGH	Random description	Tool	512504
6	XYZ	Random description	Tool	512505
7	LMN	Random description	Tool	B0001
8	QRS	Random description	Tool	B0002
9	GHR	Random description	Tool	B0003
10	KHV	Random description	Tool	B0004
11	XHDG	Random description	Tool	B0005
12	BIFK	Random description	Tool	B0011
Source2

Excel 2007
	A	B	C	D	E
2			Result
3	Part Number(From Source2)	Tool (From Source2)	Manufacturer Name (From Source1)	Tool1 (From Source1)	Tool2 (From Source1)
4	ABCD	10164	MODERN	Fair
5	EFGH	512504	ABC INC	Excellent
6	XYZ	512505	OPTEX INC	Excellent
7	LMN	B0001	CORPORATION	Excellent
8	QRS	B0002	CORPORATION	Excellent
9	GHR	B0003	CORPORATION	Excellent
10	KHV	B0004	CORPORATION	Excellent
11	XHDG	B0005	CORPORATION	Excellent
12	BIFK	B0011	HONG KONG	Excellent
Result

Thanks for your help
Rajesh

 

[22strider]

Hello Patel45

I've posted sample source and sample result data; please review.

Thanks for your help
Rajesh

 

[22strider]

bump

Could someone please review my request?

Thanks
Rajesh