Copy Paste based on comparisons calculations in 2 XL files, 1 might be .csv file .Opened in XL=Fail/Chaos

[DocAElstein]

Before:

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
1	SYMBOL
2	ACC
3	ADANIPORTS
4	AMARAJABAT
5

Worksheet: anything

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1000	1030	955.5	998.45	957.4
3	NSE	ADANIENT	EQ	27.35	27.75	25.65	25.65	25.85
4	NSE	ADANIPORTS	EQ	259	259.6	244	248.2	251.3
5	NSE	ADANIPOWER	EQ	5, 4	5, 5	5, 6	5, 7	5, 8
6	NSE	AMARAJABAT	EQ	459.8	482.25	445.1	439.35	455.35
7	NSE	AMBUJACEM	EQ	7, 4	7, 5	7, 6	7, 7	7, 8
8	NSE	APOLLOHOSP	EQ	8, 4	8, 5	8, 6	8, 7	8, 8
9

Worksheet: anything


The range to be copied is always offset by 0 rows and +2 column from the cell found, FndCel , in column B of sample1.xlsx . Its size will be 1 row and 5 columns
The range to be searched for , rngSrch is B2:B8 in sample1.xlsx , or B2:B & Lr

rngSrch :

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
2	ACC
3	ADANIENT
4	ADANIPORTS
5	ADANIPOWER
6	AMARAJABAT
7	AMBUJACEM
8	APOLLOHOSP

Worksheet: anything

In sample data are 3 FndCels

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
2	ACC

Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
2	ACC

Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
4	ADANIPORTS

Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
3	ADANIPORTS

Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

Row\Col	B
6	AMARAJABAT

Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A
4	AMARAJABAT

Worksheet: anything
( FndCels are in sample2.xlsx )


match column B of sample1.xlsx matches with column A of sample2.xlsx
if it matches then copy paste the data from column D to column H to sample2.xlsx from column B

Dim Cnt As Long
    For Cnt = 2 To Lr2
    Dim rngFnd As Range  '  http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-or-replace-entire-row?p=13007&viewfull=1#post13007
    Dim rngSrch As Range '
     Set rngSrch = Ws1.Range("B2:B" & Lr1 & "")
     Set rngFnd = rngSrch.Find(What:=Ws2.Range("A" & Cnt & "").Value, After:=Ws1.Range("B2"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) '   https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
    ' The range to be copied is always offset by 0 rows  and +2 column from the cell found, FndCel,  in column B of sample1.xlsx . Its size will be 1 row and 5 columns
     rngFnd.Offset(0, 2).Resize(1, 5).Copy '     copy column D to column H
    ' paste the data from column D to column H to sample2.xlsx from column B
     Ws2.Range("A" & Cnt & "").Offset(0, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
    Next Cnt

Full Macro here: http://www.excelfox.com/forum/showth...ll=1#post13015




After result:-

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F	G
1	SYMBOL
2	ACC	1000	1030	955.5	998.45	957.4
3	ADANIPORTS	259	259.6	244	248.2	251.3
4	AMARAJABAT	459.8	482.25	445.1	439.35	455.35
5

Worksheet: anything



Alan