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

[fixer]

Moderator notice.
This is part of the start of duplicate cross posting chaos.
When the OP, Avinash, gets caught, he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
Eventually he starts again a duplicate cross posting chaos.
When he gets caught , he post one of his canned replies , like Sorry Sir, it won’t happen again.
_….. and so on
I am not sure if he does it on purpose or is insane or he just a total dim pig shit for brains. I expect a bit of all of those….



2 months latter he is still wasting his and lots of peoples times 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.,














all files are located same place

vba is placed in a seperate file sample1.xlsm

copy all the data from column A TO COLUMN H complete data from sample2.xls and paste it to sample3.xlsb in same columns

save and close the sample2.xls and sample3.xlsb

i need to do the same by vba so plz help me in slving this problem sir

[DocAElstein]

Hi,


To copy all columns is not usually good to do. In whole columns you maybe do not heed all rows

But this is what you asked for

vba is placed in a separate file sample1.xlsm
File is attached : FileAttatchedToPost.jpg : https://imgur.com/xFLyqLQ

copy all the data from column A TO COLUMN H complete data from sample2.xls
Columns("A:H").Copy


paste it to sample3.xlsb in same columns
I recommend using the Range PasteSpecial Method
http://eileenslounge.com/viewtopic.php?f=30&t=34112#p264458
http://www.eileenslounge.com/viewtopic.php?f=27&t=25002#p193871
https://docs.microsoft.com/en-us/office/vba/api/excel.range.pastespecial

You can chose the way you want to Paste, and formats
https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype
.PasteSpecial Paste:= xlPasteValuesAndNumberFormats
.PasteSpecial Paste:= xlPasteFormats
.PasteSpecial Paste:= xlPasteColumnWidths



save and close the sample2.xls and sample3.xlsb
You can
Save
Or
Save As

I did do macro recording to check syntax:
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12531&viewfull=1#post12531

You can just Save That is enough in your case.
( You can also SaveAs )


Sub CopyAllColumns()
' copy all the data from column A TO COLUMN H complete data from sample2.xls
Workbooks("sample2.xls").Worksheets.Item(1).Columns("A:H").Copy '
' paste it to sample3.xlsb in same columns
Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Workbooks("sample3.xlsb").Worksheets.Item(1).Range("A1:H65536").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteFormats
Workbooks("sample3.xlsb").Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteColumnWidths

' save and close the sample2.xls and sample3.xlsb ' http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12531&viewfull=1#post12531
Workbooks("sample2.xls").Save
' or
Workbooks("sample2.xls").SaveAs Filename:=ThisWorkbook.Path & "\sample2.xls", FileFormat:=xlExcel8

Workbooks("sample3.xlsb").Save
' or
Workbooks("sample3.xlsb").SaveAs Filename:=ThisWorkbook.Path & "\sample3.xlsb", FileFormat:=xlExcel12

Workbooks("sample2.xls").Close: Workbooks("sample3.xlsb").Close
End Sub



Alan

[fixer]

Sub STEP4()
Dim w1 As Workbook, w2 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub



I made this code correct doc sir if there is an error sir



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Your macro is good
Your macro is working
There are no errors in it.
I did test on files enclosed , with this following macro. ( Macro is in sample1.xlsm )

Sub STEP4()
Dim w1 As Workbook, w2 As Workbook
Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
Set w2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub


Alan


1.xls : https://app.box.com/s/th2xzmkh7rnfr4qf4dho1kpgudndm073

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg.91d_Pbzklsp9zfGbIr8h gW)
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq (https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zciSZa95 9d)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg.9zaUSUoUUYs9zckCo1tv PO)
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg (https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg.9xmkXGSciKJ9xonTti2s Ix)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg.9xnskBhPnmb9xoq3mGxu _b)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg.9xm_ufqOILb9xooIlv5P LY)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG)
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM (https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg.9xmt8i0IsEr9y3FT9Y9F eM)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836 (https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[fixer]

Thnx Doc Sir for ur great help and for ur great guidance


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Hi,

Some extra info:
For Copy and Paste values, is sometimes like this: , Rng1.Value = Rng2.Value better…

To explain:-
We already saw Sub Vixer9b() ' demo for rng.Value = rng.Value
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11479&viewfull=1#post11479
http://www.excelfox.com/forum/showthread.php/2369-Calculation-by-vba?p=11485&viewfull=1#post11485

That was Rng.Value = Rng.Value – We did use .Value of Rng in two ways

We can also do rng2.Value = rng1.Value
' Or
do rng1.Value = rng2.Value


So we have Alternative for:-
Rng.Copy
Rng.PasteSpecial Paste:= xlPasteValues

It works like this:-
We can use .Value Property two ways for any range, We can do this for Rng1 , Rng2 , Rngx …. Etc…

Way 1 Put values in ( for example, Rng2 ) :-
Rng2.Value = ‘ < -------------------------------- ‘ put values in Rng2

Way 2 Get values out ( for example for Rng1 ) :-
< -------------- = Rng1.Value ‘ Get values from Rng1

So we can take values out of a range and put them in another different range: -
Rng2.Value = Rng1.Value



Macro is in
Process.xlsm



Sub STEP4b() ' Rng1.Value = Rng2.Value
Dim w1 As Workbook, w2 As Workbook
Set w1 = Workbooks.Open(ThisWorkbook.Path & "\1.xls") ' w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
Set w2 = Workbooks.Open(ThisWorkbook.Path & "\FundsCheck.xlsb") ' w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
Dim Rng1 As Range, Rng2 As Range
' If first column and first row is used , then this will work only
Set Rng1 = w1.Worksheets.Item(1).UsedRange ' or Set Rng1 = w2.Worksheets.Item(1).Range("A1:H" & Rng1.Rows.Count & "")
Set Rng2 = w2.Worksheets.Item(1).Range("A1:H" & Rng1.Rows.Count & "")

'w1.Worksheets.Item(1).Columns("A:H").Copy
'w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Let Rng2.Value = Rng1.Value

w2.Save
w2.Close
w1.Close
End Sub



Alan


1.xls : https://app.box.com/s/th2xzmkh7rnfr4qf4dho1kpgudndm073

[fixer]

Thnx Doc Sir for providing the Great info

[fixer]

Doc Sir i have a similar problem like this

Sub STEP4()
Dim w1 As Workbook, w2 As Workbook
Set w1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls") ' change the file path
Set w2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb") ' change the file path
w1.Worksheets.Item(1).Columns("A:H").Copy
w2.Worksheets.Item(1).Columns("A:H").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
w2.Save
w2.Close
w1.Close
End Sub



this code paste the data to sheet1 what i need is to paste the data in sheet2 so what i should do for the same sir plz help me sir in solving this problem sir

[DocAElstein]

Hello

Worksheets.Item(1) is first worksheet
Worksheets.Item(2) is second worksheet
Worksheets.Item(3) is Third worksheet
Workshe…….etc.

First4Worksheets.JPG : https://imgur.com/v0h9CaU
2793

Try
w2.Worksheets.Item(2).Columns("A:H").Past……………

[fixer]

Thnx Alot Doc Sir i changed the same in the code but i forgot to create a second sheet
Done Doc Sir thnx alot Sir

[fixer]

vba is placed in sample2.xlsm
all files are located in a different places
sheet name can be anything
there are two files sample1.xlsx & sample2.xlsm
match column B of sample1.xlsx matches with column A of sample2.xlsm
if it matches then copy paste the data from column D to column H to sample2.xlsm from column B
plz help me in solving this problem Sir
plz see the result i have attached the pic of the same

[DocAElstein]

Before:

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\ColA
1SYMBOL

2ACC

3ADANIPORTS

4AMARAJABAT

5
Worksheet: anything

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColABCDEFGHI
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1000
1030
955.5
998.45
957.4

3NSEADANIENTEQ
27.35
27.75
25.65
25.65
25.85

4NSEADANIPORTSEQ
259
259.6
244
248.2
251.3

5NSEADANIPOWEREQ5, 45, 55, 65, 75, 8

6NSEAMARAJABATEQ
459.8
482.25
445.1
439.35
455.35

7NSEAMBUJACEMEQ7, 47, 57, 67, 77, 8

8NSEAPOLLOHOSPEQ8, 48, 58, 68, 78, 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\ColB
2ACC

3ADANIENT

4ADANIPORTS

5ADANIPOWER

6AMARAJABAT

7AMBUJACEM

8APOLLOHOSP
Worksheet: anything

In sample data are 3 FndCels

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColB
2ACC
Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A

2ACC
Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColB
4ADANIPORTS
Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A

3ADANIPORTS
Worksheet: anything
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\ColB
6AMARAJABAT
Worksheet: anything
_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A

4AMARAJABAT
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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13015&viewfull=1#post13015




After result:-

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1SYMBOL


2ACC
1000
1030
955.5
998.45
957.4


3ADANIPORTS
259
259.6
244
248.2
251.3


4AMARAJABAT
459.8
482.25
445.1
439.35
455.35


5
Worksheet: anything



Alan

[fixer]

Sorry Doc Sir i made a mistake in the question plz relook the question Doc Sir
Sorry for the trouble sir





Moderator Translation:

I cant speak , write or understand a word of English I haven’t a clue what is written in this Thread, most of the time I haven’t got a clue what I wont and rarely even understand what I write myself, so I will post any rubbish and hope magically a code appears here that when I run it will do what I want. If not fuck thee twat helping me here: I have duplicated cross posted all over the place so maybe from there something will come of it.

[DocAElstein]

:confused:
I do not understand :confused:
What is the problem?
What have you changed?
What was the mistake you made?
What is now different to your original question?

I can see no difference in question or answer.:confused:

[fixer]

vba is placed in sample2.xlsm
and i am unable to write the code for this so its my request Doc sir for this problem plz provide me the complete code
I got many examples of code from u, but in this problem vba will not be placed in a seperate file, macro will be placed in sample2.xlsm
so plz provide me the complete code sir






Moderator Translation:

I cant speak , write or understand a word of English I haven’t a clue what is written in this Thread, most of the time I haven’t got a clue what I wont and rarely even understand what I write myself, so I will post any rubbish and hope magically a code appears here that when I run it will do what I want. If not fuck thee twat helping me here: I have duplicated cross posted all over the place so maybe from there something will come of it.

[DocAElstein]

so plz provide me the complete code sir
What is the problem with the complete macro that i gave you allready ??

ReadTheFukingPost.JPG : https://imgur.com/6UThPaD
2824

Please always read all that I post.
If you can’t / won’t / don’t then there is no point in me posting anything for you





vba is placed in sample2.xlsm...., macro will be placed in sample2.xlsm...
That makes no difference to the macros that I have written for you...

[fixer]

Sub STEP6d()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\SAMPLE1.xlsx")
Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\SAMPLE2.xlsm")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(1)
Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\SAMPLE2.xlsm") this line has errors bcoz sample2.xlsm is already opened
plz let me know what i have to right in this line

[DocAElstein]

Do you understand anything that I write???
Have you looked at the full macro that I already gave you???


and i am unable to write the code for this so its my request Doc sir for this problem plz provide me the complete code

so plz provide me the complete code sir

What is the problem with the complete macro that i gave you allready ??

ReadTheFukingPost.JPG : https://imgur.com/6UThPaD
2824
http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste?p=13016&viewfull=1#post13016
Full Macro here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13015&viewfull=1#post13015

Please always read all that I post.
If you can’t / won’t / don’t then there is no point in me posting anything for you
..I have given you the full code already
Full Macro here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13015&viewfull=1#post13015


vba is placed in sample2.xlsm
I got many examples of code from u, but in this problem vba will not be placed in a seperate file, macro will be placed in sample2.xlsm


That makes no difference to the macro that I have written for you...

[DocAElstein]

I try again to explain..

You can Open and Set a workbook like these. These all open a workbook. But they will error if the workbook is already open - That is obvious: You cannot open if it is already open!

' http://www.excelfox.com/forum/showthread.php/2421-copy-and-paste?p=13029&viewfull=1#post13029
Sub OpenAndSetWorkbook()
Dim Wb As Workbook
Workbooks.Open Filename:="C:\ ........ MyFile.xls" ' or Workbooks.Open "C:\ ........ MyFile.xls"

' Workbook "MyFile.xls" is now open ( and active*** )
Set Wb = ActiveWorkbook ' The workbook just opened will be the active*** workbook
End Sub

Sub SetAndOpenWorkbook()
Dim Wb As Workbook
' Open and Set in same code line:
Set Wb = Workbooks.Open("C:\ ........ MyFile.xls") ' this will Open and Set in same code line
End Sub

Sub OpenAndSetWorkbook_()
Dim Wb As Workbook
Workbooks.Open Filename:="C:\ ........ MyFile.xls" ' or Workbooks.Open "C:\ ........ MyFile.xls"

' Workbook "MyFile.xls" is now open ( and active ) If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks, Workbooks(" ")
Set Wb = Workbooks("MyFile.xls") ' Use workbooks collection object of open workbooks, Workbooks(" ") to reference already open workbooks
End Sub



If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks, Workbooks(" ")


Sub SetToAlreadyOpenWorkbook()
' For this to work, MyFile.xls must already be open
Dim Wb As Workbook

' If the workbook is already open , then we can refer to it using the workbooks collection object of open workbooks, Workbooks(" ")
Set Wb = Workbooks("MyFile.xls") ' Uses workbooks collection object of open workbooks, Workbooks(" ") to reference already open workbooks
End Sub

[fixer]

No Doubt Doc Sir u gave me the correct code with no errors in it
Actually i missed a small point in it and i dont know what to mentioned in that line, after u provided me the info of the same i putted that line and its 101% perfect
Thnx Alot Doc Sir and Sorry For the trouble
Thnx Alot for ur Great Support Doc Sir
Have a Great Day

[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

1Stock NameDataDataDataDataDataDataDataDataDataDataDataDa ta


2ACC
800
700
600
500
400
300
200
100
90
80
70


3ADANIENT
800
700
600
500
400
300
200
100
90
80
70


4ADANIPORTS
800
700
600
500
400
300
200
100
90
80
70


5ADANIPOWER
800
700
600
500
400
3
200
100
90
80
70


6AMARAJABAT
800
700
600
500
400
300
200
100
90
80
70


7AMBUJACEM
800
700
600
500
400
300
200
100
90
80
70


8ONGC
800
700
600
500
400
300
200
100
90
80
70


9
Worksheet: Sheet1 (2)

run macro here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13059&viewfull=1#post13059

Output results After running macro

_____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1ACC500


2ADANIENT700


3AMARAJABAT400


4
Worksheet: Tabelle2


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

[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/showthread.php?10573-Copy-and-paste-the-data-if-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

1Exchange


2NSE25


3NSE10583


4NSE17388


5NSE100
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

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEADANIENTEQ
1087
1030
955.5
998.45
1079.9
25


3NSEACCEQ
148.05
27.75
25.65
25.65
146.5
22


4NSEDLFEQ
265
419.7
350.05
387.25
267.15
10583


5NSEAMBUJACEMEQ
30.4
155.8
142.55
145.85
29.95
17388


6NSEAMARAJABATEQ
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

1ACC
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.php?thread/1227284-copy-and-paste-by-macro/&postID=1234138#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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13124&viewfull=1#post13124
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13127&viewfull=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
2869

Alan

[fixer]

Doc Sir this code has issue when i ran with 200 stocks it is doing incorrect work
So it's my request u plz remake the code for the same Doc Sir ignore this vba code
the condition written in vba language has some errors

[DocAElstein]

I see no problem with the macro from dangelor
So if I write a new macro it may also give the same error. My macro would be very similar to that for dangelor. So most likely it will also error.

Your last screenshots are useless. They tell me nothing.
I do not understand what you are showing me with the screenshots. I do not understand what error you are showing.

Try to make and upload a small file with also row data that errors.
Explain again and show me what and where the errors are….

[fixer]

Sure Doc Sir i have atttached the sample file plz run the macro and see the output Doc Sir
and plz convert alert.xlsx to alert..csv (bcoz i was not able to upload csv so i converted the file to xlsx so plz convert it into csv and then run the macro Doc Sir)

[DocAElstein]

The problem is that you are matching an empty cell
See here:- http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13133&viewfull=1#post13133

So what do you want to do if call in column I is empty??


Cell I41 is Empty:
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L

38NSECONCOREQ
368
376.6
359.5
367.8
361.1
4749
3.611
3647
364.711


39NSECUMMINSINDEQ
420.95
426.55
377.25
419.15
384.95
1901
3.8495
38875
388.7995


40NSEDABUREQ
499
503.75
494.5
499
499.05
772
4.9905
4941
494.0595


41NSEDISHTVEQ
5.1
5.15
4.75
4.95
4.75Empty
0.0475
475
4.7975


42NSEDIVISLABEQ
2410
2460
2390.6
2417.3
2425.4
10940
24.254
240115
2401.146


43NSEDLFEQ
135
135
127.6
137
128.2
14732
1.282
12945
129.482


44NSEDRREDDYEQ
4010
4049.6
3970.1
4027.1
4002.8
881
40.028
40428
4042.828
Worksheet: 1-Sheet1 27Apr_2

[fixer]

do nothing for empty cell Doc Sir

[DocAElstein]

Column Letter I is column number 9

So

For i = 2 To rg1.Rows.Count
If .Cells(i, 8) > .Cells(i, 4) Then ' if column H of 1.xls is greater than column D of 1.xls
If Not .Cells(i, 9).Value = "" Then Set c = Ws2.Columns(2).Find(.Cells(i, 9)) ' match column I of 1.xls with column B of 2.csv
If Not c Is Nothing Then 'if match found
c.Offset(, 2).Value = "<" ' put this symbol "<" in column D of 2
c.Offset(, 3).Value = .Cells(i, 11) ' copy paste the data of column K of 1.xls in column E of 2.csv
End If
Else ' if column H of 1.xls is lower than column D of 1.xls
If Not .Cells(i, 9).Value = "" Then Set c = Ws2.Columns(2).Find(.Cells(i, 9)) ' match column I of 1.xls with column B of 2.csv
If Not c Is Nothing Then 'if match found
c.Offset(, 2).Value = ">" ' then put this symbol ">" in column D of 2.csv
c.Offset(, 3).Value = .Cells(i, 11) ' copy paste the data of column K of 1.xls in column E of 2.csv
End If
End If
Next i

OR


For i = 2 To rg1.Rows.Count
If .Cells(i, 8) > .Cells(i, 4) Then ' if column H of 1.xls is greater than column D of 1.xls
If .Cells(i, 9).Value = "" Then
' do nothing
Else
Set c = Ws2.Columns(2).Find(.Cells(i, 9)) ' match column I of 1.xls with column B of 2.csv
If Not c Is Nothing Then 'if match found
c.Offset(, 2).Value = "<" ' put this symbol "<" in column D of 2
c.Offset(, 3).Value = .Cells(i, 11) ' copy paste the data of column K of 1.xls in column E of 2.csv
End If
End If
Else ' if column H of 1.xls is lower than column D of 1.xls
If .Cells(i, 9).Value = "" Then
' do nothing
Else
Set c = Ws2.Columns(2).Find(.Cells(i, 9)) ' match column I of 1.xls with column B of 2.csv
If Not c Is Nothing Then 'if match found
c.Offset(, 2).Value = ">" ' then put this symbol ">" in column D of 2.csv
c.Offset(, 3).Value = .Cells(i, 11) ' copy paste the data of column K of 1.xls in column E of 2.csv
End If
End If
End If
Next i

[fixer]

Doc Sir code is not proper, its my fault (i was not able to explained u [misunderstanding ]) let me explain
there are some datas who has a match but it was not matched by the vba code
i am attaching a sample pic of the same (there are many more matches i have attached only two sample pic)

error1 it has a match plz see but the vba code doesnt matches the same
error2 it has a match plz see but the vba code doesnt matches the same
there are many more data who has a match

[DocAElstein]

Upload file with small number of rows < 30 , to demonstrate all remaining problems
Explain what rows are not working
Last time to tell you : In forum test data just enough rows to show all problems!!!
I showed you:- http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13133&viewfull=1#post13133

Take your time and do it right. I am busy now until later...

[fixer]

Doc Sir i checked all the details No Doubt code has issue
i tried changing i=1 also but after that also i am not getting correct output
this code is working perfect with small data file but with the file which i sented u of 200 stocks it is not working
if i share less data file then it will show correct result and again u will tell me the code is perfect but with the file which i sented u its not working plz run the macro and see only the cells that doesnt has data and check the same in 1.xls it is there or not u will see it is there then question arises why the data is not pasted plz ran the macro and see it

[DocAElstein]

You are right
OK, now see what the problem is.
The macro is bad
You are right, forget the macro, it is a bad macro. There are many wrong values
Now I am busy, but tomorrow I will write you a new macro

Alan

[fixer]

No Problem Doc Sir Take ur Time
Have a Great Day

[DocAElstein]

The error is caused by bad understanding of Range.Find Method ( https://docs.microsoft.com/de-de/office/vba/api/excel.range.find )

We only need small amount of test data to demonstrate the problem: See here, for explanation of the problem: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13142&viewfull=1#post13142

I will post you a new macro later.

Alan

[DocAElstein]

Before:
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1182
1193
1151.7
1190.45
1156.6
22
11.566
116815
1168.166


3NSEADANIENTEQ
137.15
140.55
134.1
140.5
134.65
25
1.3465
13595
135.9965


4NSEADANIPORTSEQ
273.95
276.95
269.55
277.6
270.65
15083
2.7065
27335
273.3565


5NSEBERGEPAINTEQ
521
535
515
519.7
519.7
404
5.197
52485
524.897


6NSEBHARATFORGEQ
251.1
265
251.1
263.25
263.25
422
2.6325
26065
260.6175


7
Worksheet: 1-Sheet1 27Apr_2 (2)

_____ Workbook: AlertTestData.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1NSE
25
6A


2NSE
17388
6A


3NSE
404
6A


4NSE
422
6A


5NSE
10604
6A


6NSE
438
6A


7NSE
10794
6A


8NSE
1250
6A


9NSE
625
6A


10NSE
15083
6A


11NSE
22
6A


12
Worksheet: Alert.




New macro here: http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13145&viewfull=1#post13145
After running new macro

_____ Workbook: AlertTestData.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1NSE
25
6>
13595A


2NSE
17388
6A


3NSE
404
6>
52485A


4NSE
422
6<
26065A


5NSE
10604
6A


6NSE
438
6A


7NSE
10794
6A


8NSE
1250
6A


9NSE
625
6A


10NSE
15083
6>
27335A


11NSE
22
6>
116815A


12
Worksheet: Alert.




Share ‘1.xls’ : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Share ‘AlertTestData.xlsx’ : https://app.box.com/s/nhdxcq0ulxldebanz1lz49wr1stf1pc4
Share ‘macro.xlsm’ : https://app.box.com/s/599q2it3uck3hfwm5kscmmgtn0be66wt

[fixer]

Sub STEP8()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
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)
Dim Rg1 As Range, RngSrchIn As Range
Set Rg1 = Ws1.Cells.Item(1, 1).CurrentRegion
Dim Lr2 As Long: Let Lr2 = Ws2.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row
Set RngSrchIn = Ws2.Range("B1:B" & Lr2 & "")
Rem 2
Dim Cnt
For Cnt = 2 To Rg1.Rows.Count
Dim cRng As Range
Set cRng = RngSrchIn.Find(What:=Ws1.Cells.Item(Cnt, 9), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, Searchdirection:=xlNext, MatchCase:=True)
If Not cRng Is Nothing And Not cRng.Value = "" Then

If Ws1.Cells(Cnt, 8) > Ws1.Cells(Cnt, 4) Then
Let cRng.Offset(, 2).Value = "<"
Let cRng.Offset(, 3).Value = Ws1.Cells(Cnt, 11)
ElseIf Ws1.Cells(Cnt, 8) < Ws1.Cells(Cnt, 4) Then
Let cRng.Offset(, 2).Value = ">"
Let cRng.Offset(, 3).Value = Ws1.Cells(Cnt, 11)
Else

End If
Else

End If
Next Cnt

End Sub





I am getting error with this line
If Not cRng Is Nothing And Not cRng.Value = "" Then
error msg =Object variable or with block variable not set

[DocAElstein]

Try

Sub STEP8()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
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)
Dim Rg1 As Range, RngSrchIn As Range
Set Rg1 = Ws1.Cells.Item(1, 1).CurrentRegion
Dim Lr2 As Long: Let Lr2 = Ws2.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row
Set RngSrchIn = Ws2.Range("B1:B" & Lr2 & "")
Rem 2
Dim Cnt
For Cnt = 2 To Rg1.Rows.Count
Dim cRng As Range
Set cRng = RngSrchIn.Find(What:=Ws1.Cells.Item(Cnt, 9), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, Searchdirection:=xlNext, MatchCase:=True)
If Not cRng Is Nothing Then
If Not cRng.Value = "" Then

If Ws1.Cells(Cnt, 8) > Ws1.Cells(Cnt, 4) Then
Let cRng.Offset(, 2).Value = "<"
Let cRng.Offset(, 3).Value = Ws1.Cells(Cnt, 11)
ElseIf Ws1.Cells(Cnt, 8) < Ws1.Cells(Cnt, 4) Then
Let cRng.Offset(, 2).Value = ">"
Let cRng.Offset(, 3).Value = Ws1.Cells(Cnt, 11)
Else
End If
Else ' cell is empty
End If
Else

End If
Next Cnt

End Sub

[fixer]

Awesome Doc Sir
Probelm Solved
Thnx Alot for helping me in solving this problem Doc Sir

at the end of code i used
Wb2.Save
Wb2.Close
Wb1.Close

but i am getting error while saving the wb2 bcoz wb2 is a csv file
i dont want any msg asking to save it or not it should be saved automatically so plz guide for the same
i uploaded the sample pic of the msg box

[DocAElstein]

I do not understand too much about how to handle .csv files in Excel

this will prevent the display alert

Wb2.Save
Let Application.DisplayAlerts = False
Wb2.Close
Let Application.DisplayAlerts = True
Wb1.Close

But it may not give the results you want.

I do not understand too much about how to handle .csv files in Excel
There may be complicated issues to do with internal settings.
Opening and closing csv files in Excel might involve lots of complicated work. I do not have much experience in this work.

[fixer]

Wb2.Save
Let Application.DisplayAlerts = False
Wb2.Close
Let Application.DisplayAlerts = True
Wb1.Close

This worked for me Doc Sir
Thnx Alot Doc Sir
Probelm Solved

[DocAElstein]

You should maybe check also what happens if you re open the file...







Edit later… 2 months later…

The OP , Avinash, did not read this, ignored it , or didn’t understand it, and 2 months latter he is still wasting his and lots of peoples times 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.,

[fixer]

Minor changes are there in this post

Sub STEP9()
Dim Wb1 As Workbook, Wb2 As Workbook, Wb3 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
Set Wb3 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xl sx")

Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(1)
Set Ws3 = Wb3.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long, Lr As Long, Lr3 As Long
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count).End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws1.Rows.Count).End(xlUp).Row

Dim Cnt
For Cnt = 2 To Lr3
Dim VarMtch As Variant
Let VarMtch = Application.Match(CStr(Ws1.Range("I" & Cnt & "").Value), Ws2.Range("B2:B" & Lr2 & ""), 0)
If Not IsError(VarMtch) Then

Else
Ws1.Range("B" & Cnt & ",I" & Cnt & "").Copy
Let Lr3 = Lr3 + 1
Ws3.Range("A" & Lr3 & "").PasteSpecial Paste:=xlPasteValues
End If
Next Cnt

Wb1.Save
Wb1.Close
Wb2.Save
Wb2.Close
Wb3.Save
Wb3.Close


End Sub





the result was pasted in Ws22
but we have to paste the data(result to Ws3)
i changed the code and i tried to edit the same but i was unsuccessful in doing so plz see the code and change the vba code so that the result should be pasted in Ws3

[DocAElstein]

If the only change is to paste the data to Ws3, then I see just one error in your macro ,
Why have you changed to
For Cnt = 2 To Lr3 ?
It should still be
For Cnt = 2 To Lr2
The macro is going down rows in worksheet Ws2 from row 2 until the last row which is Lr2
My Lr22 = your Lr3 is the row count for data being pasted out : For each new data is needed a new row - the next row - the next row will be .. + 1

If the only change is to paste to Ws3 , then my original macro is only needed to be changed in 5 places



Sub Step11b() ' 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, Wb3 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(" ")
Set Wb3 = .......
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 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)
Set Ws3 = Wb3.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
Ws3.Range("A" & Lr22 & "").PasteSpecial Paste:=xlPasteValues
End If
Next Cnt
End Sub



or if you prefer to use a different variable for the row count in Ws3 , Lr3 , then



Sub Step11b() ' 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, Wb3 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(" ")
Set Wb3 = .......
Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 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)
Set Ws3 = Wb3.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long, Lr As Long, Lr3 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 Lr3 = Lr3+ 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
Ws3.Range("A" & Lr3 & "").PasteSpecial Paste:=xlPasteValues
End If
Next Cnt
End Sub

[fixer]

Problem Solved
Thnx Doc Sir for helping me in solving this problem Sir
Have a Great Day Sir

[fixer]

Hello Everyone, I am looking for a macro plz see the details below


Condition 1) Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column B of sample1.xls with column B of sample2.csv & if it is there then do nothing & if it is not present then paste the column B data of sample1.xls to column B of sample2.csv


Condition 2) Only If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls then Match Column B of sample1.xls with column B of sample2.csv & if it is there then do nothing & if it is not present then paste the column B data of sample1.xls to column B of sample2.csv


Plz Note
All files are located in a different path (So in the macro the path will be hardcoded, Assume any path & I will edit the path manually by myself)
Sheet name can be anything
Macro will be putted in a vba.xlsm


Plz see the file that I have attached below


I have explained the details more deeply now, Any doubts plz ask



http://www.vbaexpress.com/forum/showthread.php?67471-copy-paste-the-data-if-condition-matches

Download a csv file from below link
https://drive.google.com/file/d/1Ec2plYd0oZq-v7r6itJYOf8BjOLtY3VL/view?usp=sharing



cross post .. another !!!!
https://www.excelforum.com/excel-programming-vba-macros/1318109-copy-paste-the-data-if-condition-matches-with-dragging-macro.html#post5342868

[DocAElstein]

I think you may have screwed up again and once again given the wrong explanation….

... Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column B of sample1.xls with column B of sample2.csv....… I think you mean…… Match Column I of sample1.xls with column B (Field 2) of sample2.csv

Field 2(column B) values in your Sample2.csv comma separated values text file:

NSE,
101010,
6,<,
12783,A,,,,,GTT

NSE,
22,
6,<,
12783,A,,,,,GTT

NSE,
17388,
6,<,
12783,A,,,,,GTT


Column I of Sample1.xls
Row\Col
I

1

2
17388

3
100

4
15083

5
25

6
22

7




Here is your supplied text file, Sample2.csv:
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13475&viewfull=1#post13475

Here is the corrected explanation that I use…
Either of these 2 conditions are to result in macro will copy value in column I of sample1.xls to field 2(column B) of text file Sample2.csv on a new line:
Condition 1) Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of sample2.csv & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of sample2.csv

Condition 2) Only If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of sample2.csv & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of sample2.csv

OR

__ ( If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls ) or ( If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls ) Then
____
Match Column I of sample1.xls with second field values (column B) of sample2.csv & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of sample2.csv

Before:
_____ Workbook: Sample1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1295.83
1310
1280.25
1270.2
1310
17388SHORT
1308.79


3NSEADANIENTEQ
151.5
154.9
150
148.35
155
100SHORT
153.015


4NSEADANIPORTSEQ
329.26
336.35
326
323.8
331
15083BUY
325.967


5NSEADANIPOWEREQ
39.6
40
37.75
36.4
39
25SHORT
39.204


6NSEAMARAJABATEQ
636.3
655.7
630
614.6
600
22SHORT
629.937
Worksheet: Sample1 5June

Sample2.csv
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13475&viewfull=1#post13475
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13475&viewfull=1#post13476




After: running macro here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13478&viewfull=1#post13478

Sample2After.csv
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13477&viewfull=1#post13477
Sample2After.csv : https://app.box.com/s/0j4118cwzzofe76ytb5rqkvz3qj0vseu


NSE,101010,6,<,12783,A,,,,,GTT
NSE,22,6,<,12783,A,,,,,GTT
NSE,17388,6,<,12783,A,,,,,GTT
,100,,,,,,,,,,
,25,,,,,,,,,,



Alan



sample2.csv : https://app.box.com/s/0ej2h41g9fvm94cflf2j60a8o6p3334t
Sample1.xls : https://app.box.com/s/xh58fgjl74w06hvsd53jriqkohdm6a3q
macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
Sample2After.csv : https://app.box.com/s/0j4118cwzzofe76ytb5rqkvz3qj0vseu
vba.xlsm : https://app.box.com/s/juekenyll42z84j6ms7qonzsngnugoyo

[fixer]

Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "Sample2.csv"
Path is different, It is not in the same path when i put this
Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample2.csv")
I got error

[DocAElstein]

Hi Avinash, - remember I expect you to read all my reply. I don’t expect you to understand it all and I will keep helping you further on this issue, - but you know I will not reply quickly. So use the time to read carefully and slowly all I write here for you….…


Once again your reply makes no sense!! Your reply is nonsense.
I still think that you have not understood anything about text files. You have understood still nothing about the .csv story…..




Same story and reply as as here: https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files?p=13433&viewfull=1#post13433

Same answer as here… https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files#post13493

....This makes no sense, since you are comparing two different things. I have explained that to you in great detail many times already. .... ….etc… etc….



Let me try to explain again….
You know that code lines like this , Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample2.csv") , will ( try ) to open a file. It would open an excel file, no problem. It may or may not work to open a text file. Sometimes it may work. Sometimes it may not work. You must know this, because lots of people have told you all this hundreds of times already!!!

In my macro I do not have any line like that for sample2.csv

Why do I not have any line like that for sample2.csv?
Because (bcoz ) . …..
sample2.csv is a text file!!!!!!
My macro does not open a text file with Excel
Also this macro https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13431&viewfull=1#post13431 does not open a text file with Excel
HansV does not try to open a text file with Excel
Logit here https://chandoo.org/forum/threads/fetching-data-from-notepad-to-excel.44312/#post-264364 , does not try to open a text file with Excel
I do not try here http://www.eileenslounge.com/viewtopic.php?p=269105#p269105 at Eileen’s Lounge to open a text file with Excel
jindon at excelforum here https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5340067 does not try to open a text file with Excel

No one anywhere ( except you ) is trying to open a text file in Excel!!!
Forget Opening text file in Excel. That is stupid. It can be done but only sometimes will work!!!!

We are all not opening a text file in Excel. We are manipulating a text file using VBA. We use things like this, I already told you hundreds of times… https://www.homeandlearn.org/open_a_text_file_in_vba.html - We all open a text file in/ with Excel VBA - we do not ry to open a text file in/ with Excel. To open a text file with Excel is bad, stupid, dangerous. It may not work and may cause many problems later. You have already wasted many weeks of your time and others to open a text file with Excel. It has often given you problems. You go around and around in circles and get no where because of this stupidity!!


( If you really want to open the text file with Excel, then you can try to do it after the problem is solved. – I already did it at the end of the macro, to get this: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13431&viewfull=1#post13431
Also, look at the end of the macro:

'Rem 6 Check File in Excel VBA open
'' Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "Sample2.csv"
' Workbooks.Open Filename:=ThisWorkbook.Path & Application.PathSeparator & "Sample2After.csv" ' CHANGE TO SUIT
End Sub
If you want to open the text file with Excel then do like this:

Rem 6 Check File in Excel VBA open
' Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "Sample2.csv"
Workbooks.Open Filename:=ThisWorkbook.Path & Application.PathSeparator & "Sample2After.csv" ' CHANGE TO SUIT
End Sub
OR like this:

Rem 6 Check File in Excel VBA open
' Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "Sample2.csv"
' Workbooks.Open Filename:=ThisWorkbook.Path & Application.PathSeparator & "Sample2After.csv" ' CHANGE TO SUIT
Dim Wb As Workbook
Set Wb = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample2.csv")
End Sub )

But that all has nothing to do with the issue of this Thread
If you must, just for fun, you can try to open the file in Excel only at the end of the macro , after the macro has solved the issue.
The macro solves the issue. It solves the problem. But it does not need to open a text file with Excel – it solves the problem by manipulating a text file using VBA


Let me try again to explain….
My macro solves your problem. It answers your question. It works perfectly.
But, my macro needs to know where ( the path ) to text file is……
This code line tells it where the text file is

Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "Sample2.csv" ' ' CHANGE TO SUIT ' "sample2 ef 5 June.csv" From Avinash : https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13470&viewfull=1#post13470 sample2 ef 5 June.csv : https://app.box.com/s/0j4118cwzzofe76ytb5rqkvz3qj0vseu

Question:
.....
Path is different, It is not in the same path ......

Answer: ' CHANGE TO SUIT

Let PathAndFileName = "C:\Users\WolfieeeStyle\Desktop\sample2.csv"

( Forget this .._
Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample2.csv")
_.. that is nonsense and has nothing at all to do with any of the issues in your original question!!!! )



I cant reply again here for a few hours. So take your time to read all I have written, before any replies…
:)

Alan

[fixer]

Getting Error

[DocAElstein]

Please upload the text file ( the file with .csv at the end of its name) that you are using.
( You can remove some of the first lines if you want - I only need to have the last few lines - (But I need a file, not a screenshot) )

[fixer]

I already uploaded all the files& I am runing the macro on the same file which I uploaded here

[DocAElstein]

I re downloaded, and re ran the macro (Sub VBAAppendDataToTextFileLineBasedOnTheTextFileAndEx celFileConditions() : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13478&viewfull=1#post13478 )

This is the file I used: https://app.box.com/s/0ej2h41g9fvm94cflf2j60a8o6p3334t , downloaded from post #1 , https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13470&viewfull=1#post13470

I don’t get that error. So I am not sure what the problem is.
Try again.
If you still get that error, let me know, then I will think again, and maybe try something else.

[fixer]

Yes Doc Sir, I am getting that Error Again

[DocAElstein]

Run this macro in the uploaded file ( remember to change the file path and name to suit )
Then save the file and return it to me


Sub OpenSample2_csv_8June() '
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim FilePathandName As String
Let FilePathandName = ThisWorkbook.Path & Application.PathSeparator & "Sample2.csv" ' ' CHANGE TO SUIT
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim RwCnt As Long, TextFileLineIn As String
Open FilePathandName For Input As #FileNum 'Open Route to data
Do While Not EOF(FileNum)
Line Input #FileNum, TextFileLineIn
Let Ws1.Range("A" & Ws1.Range("A" & ActiveSheet.Rows.Count & "").End(xlUp).Row + 1 & "").Value = Left(TextFileLineIn, 4)
Let Ws1.Range("B" & ActiveSheet.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row + 1 & "").Value = Len(TextFileLineIn)
Loop

Close #FileNum
End Sub




Test2csvContents.xls : https://app.box.com/s/2knx1msb84ga5phpictm0c6bjcdnai6f

[fixer]

Plz See

[DocAElstein]

Please use the file I uploaded
https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13591&viewfull=1#post13591
and after running the macro, return the file to me


Run this macro in the uploaded file .....
Then save the file and return it to me

( change the path to suit in the macro in the uploaded file )

[fixer]

Plz see

[DocAElstein]

You are not using the file that you uploaded in post #1
I need the file that you are using!!!

Please upload the text file ( the file with .csv at the end of its name) that you are using........

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

AvinashFile Yous are usingAlanFile You uploaded in post #1

NSE,
26NSE,30,6,<,12783,A,,,,,GTTNSE,
43NSE,101010,6,<,12783,A,,,,,GTT,,,,,,,,,,,,,

NSE,
25NSE,28,6,<,1499,A,,,,,GTTNSE,
39NSE,22,6,<,12783,A,,,,,GTT,,,,,,,,,,,,,

NSE,
25NSE,25,6,<,3282,A,,,,,GTTNSE,
42NSE,17388,6,<,12783,A,,,,,GTT,,,,,,,,,,,,,

NSE,
24NSE,22,6,<,382,A,,,,,GTT,100
26,100,,,,,,,,,,,,,,,,,,,,,,

,25,
25,25,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
53,,,,,,,,,,,,,Only for understanding purpose,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
46,,,,,,,,,,,,,Before runing the macro,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
43,,,,,,,,,,,,,NSE,101010,6,<,12783,A,,,,,GTT

,,,,
39,,,,,,,,,,,,,NSE,22,6,<,12783,A,,,,,GTT

,,,,
42,,,,,,,,,,,,,NSE,17388,6,<,12783,A,,,,,GTT

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
45,,,,,,,,,,,,,After runing the macro,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
23,,,,,,,,,,,,,,,,,,,,,,,

,,,,
43,,,,,,,,,,,,,NSE,101010,6,<,12783,A,,,,,GTT

,,,,
39,,,,,,,,,,,,,NSE,22,6,<,12783,A,,,,,GTT

,,,,
42,,,,,,,,,,,,,NSE,17388,6,<,12783,A,,,,,GTT

,,,,
26,,,,,,,,,,,,,,100,,,,,,,,,

,,,,
25,,,,,,,,,,,,,,25,,,,,,,,,
Worksheet: Sheet1

[fixer]

Plz Download
https://drive.google.com/file/d/1C5viFM5P3UiKev3AGSnFqRoYW-SBEL4i/view?usp=sharing

[DocAElstein]

My macro assumed you had in the first field of the text file
NSE
But your latest file has either
NSE
Or
"NSE

This is your latest file:
NSE,101010,6,<,12783,A,,,,,GTT
NSE,22,6,<,12783,A,,,,,GTT
NSE,17388,6,<,12783,A,,,,,GTT
"NSE,",30,,,,,,,,,
"NSE,",26,,,,,,,,,
"NSE,",29,,,,,,,,,


But that does not explain the error that you showed here : https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13530&viewfull=1#post13530 )


My best guess is that the problem is that you have no idea most of the time what files you are using, since, for example this was rubbish:
...I am runing the macro on the same file which I uploaded here

So the original error ( https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13530&viewfull=1#post13530 ) , probably came because you were using a different file again!!!



So far you have been using at least 3 different files and telling me that they are the same one!!!



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

Text file You uploaded in post #15text file You were using post #13Text file You uploaded in post #1

NSE,101010,6,<,12783,A,,,,,GTTNSE,30,6,<,12783,A,,,,,GTTNSE,101010,6,<,12783,A,,,,,GTT,,,,,,,,,,,,,

NSE,22,6,<,12783,A,,,,,GTTNSE,28,6,<,1499,A,,,,,GTTNSE,22,6,<,12783,A,,,,,GTT,,,,,,,,,,,,,

NSE,17388,6,<,12783,A,,,,,GTTNSE,25,6,<,3282,A,,,,,GTTNSE,17388,6,<,12783,A,,,,,GTT,,,,,,,,,,,,,

"NSE,",30,,,,,,,,,NSE,22,6,<,382,A,,,,,GTT,100,,,,,,,,,,,,,,,,,,,,,,

"NSE,",26,,,,,,,,,,25,,,,,,,,,,,,,,,,,,,,,,

"NSE,",29,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,Only for understanding purpose,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,Before runing the macro,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,NSE,101010,6,<,12783,A,,,,,GTT

,,,,,,,,,,,,,NSE,22,6,<,12783,A,,,,,GTT

,,,,,,,,,,,,,NSE,17388,6,<,12783,A,,,,,GTT

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,After runing the macro,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,NSE,101010,6,<,12783,A,,,,,GTT

,,,,,,,,,,,,,NSE,22,6,<,12783,A,,,,,GTT

,,,,,,,,,,,,,NSE,17388,6,<,12783,A,,,,,GTT

,,,,,,,,,,,,,,100,,,,,,,,,

,,,,,,,,,,,,,,25,,,,,,,,,
Worksheet: Sheet1

Further....
In post #11 you must have used a different file to any of those 3, so we have at least 4 different files that you are using,

It continues to be almost impossible to help you because you either understand almost nothing I write, or you ignore almost all I write.
You quickly post rubbish in post replies.
You continue to waste everybody’s time, including your own!!

[fixer]

I Used in post #13
is different
what i have done i placed the macro in a book1.xlsm file & i changed the path in the code & i got this result after runing the macro in book1.xlsm & save the book1.xlsm & sent u the book1.xlsm file
So that was my mistake i ran ur macro incorrectly

[DocAElstein]

So, currently , at this point in time, I have no idea what you want because, as usual, you are posting a lot of mixed up rubbish.

I am happy to help further.

But you are going to have to try to explain in English what the problem is.
Take your time, and think carefully, and try to explain.
Stop posting quick nonsense replies.

No one anywhere can help if you continue to post a lot of mixed up rubbish in your replies.

[fixer]

Either of these 2 conditions are to result in macro will copy value in column I of sample1.xls to field 2(column B) of text file Sample2.csv on a new line:
Condition 1) Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of sample2.csv & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of sample2.csv

Condition 2) Only If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of sample2.csv & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of sample2.csv


I need the macro for the same Doc Sir

[DocAElstein]

:confused:
Why are you posting the question again
:confused:
OK, maybe you did wrote it a bit better. .... good

But the answer is the same..
Second part of this post for explanation : https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13482&viewfull=1#post13482
Macro https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13478&viewfull=1#post13478

So if you are not getting what you want, you must try very carefully to explain what the problem is, and supply the corrrect files and tell me what is the error etc....etc..

Maybe, now you need to go back here :
https://excelfox.com/forum/showthread.php/2505-copy-paste-the-data-if-condition-matches?p=13530&viewfull=1#post13530
and start again but upload the correct text file!!!

Your problem is like almost always, very very simple to answer...
But you are totally incapable of communicating in English, so cannot ask the question or explain the problem.
We all waste out time because you understand almost no English, and cannot communicate in English

[fixer]

Download file link
https://drive.google.com/file/d/1cpu-OlSPbPjqgSkcYuwLGvikWFZ4CFTN/view?usp=sharing
https://drive.google.com/file/d/1MBRgr7fkET6-SFo8_jxdTJJLxv3OcutZ/view?usp=sharing

Explanation of the problem:
Either of these 2 conditions are to result in macro will copy value in column I of sample1.xls to field 2(column B) of text file Sample2.csv on a new line:
Condition 1) Only If column K of sample1.xls is greater than Column D of sample1.xls & Column H of sample1.xls is Greater than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of sample2.csv & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of sample2.csv

Condition 2) Only If column K of sample1.xls is lower than Column D of sample1.xls & Column H of sample1.xls is lower than column K of sample1.xls then Match Column I of sample1.xls with second field values (column B) of sample2.csv & if it is there then do nothing & if it is not present then paste the column I data of sample1.xls to append second field values (column B) of sample2.csv


After runing the macro given by you causing Error

Error details- "Input past end of file"
Highlighted line in the module:
Line Input #FileNum, TextFileLineIn ' next line in text file

[fixer]

We will not play with .csv files
Doc Sir do not work on this problem
changing the .csv files with .xlsx
My system also accepts Notepads files
so what i will do is i will prepare the data in .xlsx file and then convert all that data from.xlsx to notepad
Plz leave this problem ignore it
I dont want macro for the same
Thnx Alot For ur Great Help

[DocAElstein]

This is the file that you uploaded : https://imgur.com/Ooc7WBU
( https://drive.google.com/file/d/1MBRgr7fkET6-SFo8_jxdTJJLxv3OcutZ/view?usp=sharing )
It is the same text file that you gave in post#1
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13475&viewfull=1#post13475


If I run my macro given to you, using that complete file , then I do not get that error!

Are you sure you get the error when running the macro with that file?

If not…
I need the text file that you are using when you get that error


( I think we are back now to where we started yesterday..... )

[fixer]

Yes but this was the last misunderstanding happened
I converted all .csv file to .xlsx file
Bcoz playing with .csv file are difficult & we should work smartly, why we should work Hard If the problem can be solved Smartly & easily
My system Accepts notepad file also, So I dont need .csv files now

[DocAElstein]

....
My system also accepts Notepads files... then convert all that data from.xlsx to notepad
.....You are talking rubbish again
There are no such things as notepad files. I told you this hundreds of times

[DocAElstein]

I think your problem is very very simple

I just need the text file, the sample2.csv, that gives you that error

[fixer]

Doc Sir No probelm
I will share it to u
But if i continue with.csv file Now and in future ask question then again it will create trouble
So its better to stay away from .csv file & macro related to .csv files
plz see my new 2 question & u will understand what i have done & u will like it
I hope So

[DocAElstein]

OK
But I would like to solve this problem because i have already done so much work on it

How about this...

Give me 2 text files

sample2Before.csv
- This will be the file that gives you the error

sample2After.csv
- This is excactly the file that you want after.

or just give me the one file that gives the error

[fixer]

Plz download it from below link
https://drive.google.com/file/d/1TyfOWXhZ9Psg7Z4XhngWwzZ3s43YxzwA/view?usp=sharing
https://drive.google.com/file/d/1X2MdidDmJ886I6HwJLvIqNATRC34o5hD/view?usp=sharing

[DocAElstein]

OK, good, I see the problem.
I will post the solution later .

[DocAElstein]

The problem is that text files cannot be analysed by a worksheet type way for like last row
Lr= ….. .End(Xlup).row ….… and so on. That does not work in a text file. We do not have rows in a text file

In a text file, we talk about instead lines and records

Excel file row is only approximately = Text file line or Text file record

In text file for last record, or last line, we use
EOF ( https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/eof-function )

Or

we do it another way. Your file in post#1 was like
NSE, ,
NSE, ,
NSE, ,
, ,
, ,
, , Explanation etc .

So I did do check for “NSE,” for last data that you wanted

So for no extra lines like below, like in sample2BEFORE.csv , it does not work too well
NSE, ,
NSE, ,
NSE, ,


( See here for more info, just for info, not important :
Original text files : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page32#post13475
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page32#post13476

Text files post#29 : https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13618&viewfull=1#post13618 )



So solution to this problem
Change .._

Do While Left(TextFileLineIn, 4) = "NSE," ' For text file lines like NSE,101010,6,<,12783,A,,,,,GTT that may have extra unwanted lines like in one Avinash uses stupidly for explanations
Let RwCnt = RwCnt + 1
Line Input #FileNum, TextFileLineIn ' next line in text file
Loop

-..To this

Do While Not EOF(FileNum) = True And Left(TextFileLineIn, 4) = "NSE," ' Left(TextFileLineIn, 4) = "NSE," ' For text file lines like NSE,101010,6,<,12783,A,,,,,GTT that may have extra unwanted lines like in one Avinash uses stupidly for explanations
Let RwCnt = RwCnt + 1 ' for first and subsequent lines given by below. ... but
Line Input #FileNum, TextFileLineIn ' next line in text file
Loop
If EOF(FileNum) = True Then Let RwCnt = RwCnt + 1 ' ... but if the last line I want is EOF, I will not catch it in the loop so must add a 1 here

Full macro here:
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13619&viewfull=1#post13619





Share ‘sample2BEFORE.csv’ : https://app.box.com/s/d8lu7iatfv6h8spp9eru8asm3h4v4e4p
Share ‘Sample2After.csv’ : https://app.box.com/s/0j4118cwzzofe76ytb5rqkvz3qj0vseu
vba.xlsm : https://app.box.com/s/juekenyll42z84j6ms7qonzsngnugoyo
Sample1.xls : https://app.box.com/s/xh58fgjl74w06hvsd53jriqkohdm6a3q
macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk

[fixer]

Thnx Alot Doc Sir for Helping me in solving this problem
& What u suggest, I completely agree with not to play with CSV file & I have decided I will change .CSV to XLSX & then from XLSX to notepad
& I will upload notepad data to my system not the CSV file

It is the best way,I think

What u suggest?

[DocAElstein]

… change .CSV to XLSX & then from XLSX to notepad
& I will upload notepad data to my system not the CSV fileI tell you now already 101 times… there are no such things as notepad files , notepad data … etc… you mean text files




Biggest Problem is not text files

Excel files sometimes have problem. Excel files sometimes have no problem
Text files sometimes have problem. Text files sometimes have no problem
It makes no difference to use only Excel , or only text, or both. Problems remain the same

Using Excel to manipulate text is mostly no problem if you do it properly, like https://www.homeandlearn.org/open_a_text_file_in_vba.html
To open in Excel or to save a file from Excel to text can give problems often.

It makes no difference for Avinash to use text files or Excel files
You do not understand VBA or Excel or text files. This is a problem. It makes no difference if you use text or Excel. This is a problem, but not the biggest problem..

Biggest problems are
_ Avinash does not understand English
_ Avinash replies too quickly, does not read or understand, and makes mistakes
So we all waste lots of time

Example
We wasted a whole day because
_ Avinash did not understand this:
____ Upload the file that gives you the error
_ Avinash made mistakes and uploaded wrong files
That was big problem.
The problem with the macro was very small.






& What u suggest, I completely agree with not to play with CSV file & I have decided I will change .CSV to XLSX & then from XLSX to notepad
& I will upload notepad data to my system not the CSV file I am not really sure ahat you mean, but
Biggest problems are from converting/ changing from text ( .csv ) to Excel or Excel to text etc..

Excel files should stay Excel files
Text files ( .csv) should stay text files.

My macros, ( and jindons macros ) have Excel file stay as Excel File, Text file stay as text file.
No changing or converting Excel to text, text to excel
This is best.




( I do not have anymore time for you today. I look again tomorrow
:) )

[fixer]

Yes plz, but do not look this question
This is Solved
Problem Solved