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

[fixer]

Thnx Alan Elston Sir (Doc Sir)

[fixer]

vba is placed in a seperate file macro.xlsm
there are two files 1.xlsx & 2.xlsx
all files are located in a different place
2.xlsx file is blank file it doesn't have any data
in 1.xlsx i have data (i have attached the sample pic of the same)
now what i want is see the yellow highlighted colour data and if yellow highlighted colour data is greater than 5 or equal to 5 then copy the stock name and paste it to 2.xlsx
i have attached the sample pic of the result it will be pasted to 2.xlsx from 1.xlsx
so plz have a look sir and help me out in solving this problem sir

[DocAElstein]

Hi
There are very many different ways to do something like this.
So this solution would be just one of many ways.

Example:

Before:

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

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Stock Name	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data	Data
2	ACC	800	700	600	500	400	300	200	100	90	80	70
3	ADANIENT	800	700	600	500	400	300	200	100	90	80	70
4	ADANIPORTS	800	700	600	500	400	300	200	100	90	80	70
5	ADANIPOWER	800	700	600	500	400	3	200	100	90	80	70
6	AMARAJABAT	800	700	600	500	400	300	200	100	90	80	70
7	AMBUJACEM	800	700	600	500	400	300	200	100	90	80	70
8	ONGC	800	700	600	500	400	300	200	100	90	80	70
9

Worksheet: Sheet1 (2)

run macro here: http://www.excelfox.com/forum/showth...ll=1#post13059

Output results After running macro

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

Row\Col	A	B	C
1	ACC	500
2	ADANIENT	700
3	AMARAJABAT	400
4

Worksheet: Tabelle2

---

macro is here : https://imgur.com/Rs0EaVf , and also in uploaded file.
macro is here.JPG

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem

[fixer]

All files are located in different path
vba will be placed in a macro.xlsm
i have a file name 1.xls & 2.xls
match column I of 1.xls with column B of 2.xls
If column I of 1.xls matches with column B of 2.xls then do nothing &
if column I of 1.xls doesnt matches with column B of 2.xls then copy and paste the column B & Column I of 1.xls to column A & column B of sheet2 of 2.xls
sheet name can be anything
plz see the sample pic & help me in solving this problem by vba

the bigger pic is 1.xls
the smallest pic is sheet2 of 2.xls (result)

---

Moderator notice...
Yet again another cross post
https://www.excelguru.ca/forums/show...-condition-met

[DocAElstein]

I think you have not explained correctly what you want.
Your question explanation does not match you sample data.
Once again you have incorrectly explained what you want.

This is wrong!!!
If column I of 1.xls matches with column B of 2.xls then do nothing &
if column I of 1.xls doesnt matches with column B of 2.xls then copy and paste the column B & Column I of 1.xls to column A & column B of sheet2 of 2.xls
It is rubbish. It does not explain your test data.

Once again I must try to guess what you want!

This is my guess:
Consider the value in each row of column I of 1.xls, starting from row 2
If the value from that row of column I of 1.xls is also in any row of column B of the first worksheet in 2.xls , then
do nothing.
Else If the value from that row of column I of 1.xls is not to be found in any row of column B of the first worksheet in 2.xls, then do the following:
Copy the value from columns B and I for that row of 1.xls and paste them to columns A and B of the second worksheet of 2.xls

Before:
_____ Workbook: 2.xls ( Using Excel 2007 32 bit )

Row\Col	A	B
1	Exchange
2	NSE	25
3	NSE	10583
4	NSE	17388
5	NSE	100

Worksheet: Sheet1 (2)
_____ Workbook: 2.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1
2
3

Worksheet: Sheet2

_____ Workbook: 1.xls ( 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	ADANIENT	EQ	1087	1030	955.5	998.45	1079.9	25
3	NSE	ACC	EQ	148.05	27.75	25.65	25.65	146.5	22
4	NSE	DLF	EQ	265	419.7	350.05	387.25	267.15	10583
5	NSE	AMBUJACEM	EQ	30.4	155.8	142.55	145.85	29.95	17388
6	NSE	AMARAJABAT	EQ	502	514.85	502	499.05	507.45	100

Worksheet: 1-Sheet1

After results
_____ Workbook: 2.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1	ACC	22
2

Worksheet: Sheet2

Macro:

Sub Step11() '    http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste-by-VBA-based-on-criteria?p=13110&viewfull=1#post13110      http://www.excelfox.com/forum/showthread.php/2458-Copy-and-paste-the-data-if-condition-met
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook   '                           If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb1 = Workbooks("1.xls")         ' Workbooks("1.xlsx")         '          Workbooks("sample1.xlsx")   '                                                 Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls")                ' w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")           ' change the file path   If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
 Set Wb2 = Workbooks("2.xls")         ' Workbooks("2.xlsx")         '          Workbooks("sample2.xlsx")   '                                                 Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb")      ' w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path      If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks,  Workbooks(" ")
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws22 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)      '                                                                            Set Ws1 = Wb1.Worksheets("anything")  '     sheet name can be anything
 Set Ws2 = Wb2.Worksheets.Item(1)      '                                                                          ' Set Ws2 = Wb2.Worksheets("anything")
 Set Ws22 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long, Lr As Long, Lr22 As Long
 Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row      '   http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466      Making Lr dynamic ( using rng.End(XlUp) for a single column. )
 Let Lr2 = Ws2.Range("A" & Ws1.Rows.Count).End(xlUp).Row
' Let Lr = IIf(Lr2 > Lr1, Lr2, Lr1)
Rem 2 do it
Dim cnt
    For cnt = 2 To Lr2
    Dim VarMtch As Variant
     Let VarMtch = Application.Match(CStr(Ws1.Range("I" & cnt & "").Value), Ws2.Range("B2:B" & Lr2 & ""), 0) ' We look for the string value from each row in column I of Ws1 in the range of column B in Ws2
        If Not IsError(VarMtch) Then ' If we have a match, then  Application.Match  will return an integer of the position along(down) where the match is found
        ' do nothing
        Else '  Application.Match  will return a VB error string if no match could be found
         Ws1.Range("B" & cnt & ",I" & cnt & "").Copy  ' if ranges are "in line" - that is to say have the same "width" ( in this example a single row width ) , then VBA lets us copy this to the clipboard
         Let Lr22 = Lr22 + 1 ' next free row in second worksheet of 2.xls
         Ws22.Range("A" & Lr22 & "").PasteSpecial Paste:=xlPasteValues ' Pasting of copied values which were "in line" allows us to paste out, but the missing in between bits ( columns in this example ) are missed out - the ranges are put together. Co incidentally we want this output in this example
        End If
    Next cnt
End Sub

[fixer]

Copy and Paste based on comparisons and calculations of cells in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos

Moderator Notice:
This is the start of two months of wasting everybodies time going around in circles making a total mess in posts everywhere because he refuses to understand anything at all about Text files.
The biggest problem is in using an Excel object to open a .csv File, which is usually not a good idea.
Sometimes you might get the impression he is understanding at least a small part of the problem, but wither it is just co incidence that what he has pasted infers that and he has no idea what he is writing, or two seconds later he forgets and we are back to the starting point and he starts again duplicating cross posting the same question… - I think there is a good chance the OP is insane, and certainly a total dim pig shit for brains. Physical violence is I think the only hope for him, and if all else fails then he should be put to death
3 Bans and he still just goes off and starts again at another forum, gets no where , annoys everyone, then comes back here again and starts agin here making exactly the same mistakes...

---

All files are located in a different path

vba code will be placed in a macro.xlsm

i have two files 1.xls & 2.csv

check wheather column H of 1.xls is greater or lower than column D of 1.xls

if column H of 1.xls is greater than column D of 1.xls then match column I of 1.xls with column B of 2.csv & if it matches then put this symbol "<" in column D of 2.csv & copy paste the data of column K of 1.xls in column E of 2.csv

or

if column H of 1.xls is lower than column D of 1.xls then match column I of 1.xls with column B of 2.csv & if it matches then put this symbol ">" in column D of 2.csv & copy paste the data of column K of 1.xls in column E of 2.csv



save and close both the file

plz help me in solving this problem by vba



i have a vba code but has a little error plz have a look sir

Sub STEP8()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim rg1 As Range, i As Long, c As Range
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(1)
Set rg1 = Ws1.Cells(1, 1).CurrentRegion
With rg1
    For i = 2 To rg1.Rows.Count
    If .Cells(i, 8) > .Cells(i, 4) Then
    Set c = Ws2.Columns(2).Find(.Cells(i, 9))
    If Not c Is Nothing Then 'if match found
    c.Offset(, 2).Value = "<"
    c.Offset(, 3).Value = .Cells(i, 11)
    End If
    Else
    Set c = Ws2.Columns(2).Find(.Cells(i, 9))
    If Not c Is Nothing Then 'if match found
    c.Offset(, 2).Value = ">"
    c.Offset(, 3).Value = .Cells(i, 11)
    End If
    End If
    Next i
End With
End Sub

I was unable to upload alert..csv so i am uploading a pic of the same sir So plz have a look and help me out in solving this problem Sir

[DocAElstein]

The test data in 1.xls is much too large for help in a forum. There are too many rows to post.
We just need enough data to test macro.
It is difficult in a forum to work with many rows.
Reduce the rows
We need just enough data to test.
Pick your test data carefully.
Just use a few rows. But pick your test data carefully so that it test all scenarios

---

What does this mean?

i have a vba code but has a little error

You must give more details

---

From where do you have this macro(vba code)?

Alan

[fixer]

https://www.ozgrid.com/forum/index.p...38#post1234138

I have uploaded the sample file plz have a relook Doc sir
highlighted colour is only for understanding purpose in actual file there will not be aby highlighted colour

[DocAElstein]

I see no problem with the macro from dangelor
It seems to work when I test – See here:- : http://www.excelfox.com/forum/showth...ll=1#post13124
http://www.excelfox.com/forum/showth...ll=1#post13127


I see no problem.. I get the correct results.

Or maybe this is problem in your macro …..

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")

https://imgur.com/cfLWkz6
Two Dots.JPG

Alan