No Problem Doc Sir Take ur Time
Have a Great Day
Printable View
No Problem Doc Sir Take ur Time
Have a Great Day
The error is caused by bad understanding of Range.Find Method ( https://docs.microsoft.com/de-de/off...cel.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/showth...ll=1#post13142
I will post you a new macro later.
Alan
Before:
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Worksheet: 1-Sheet1 27Apr_2 (2)
Row\Col Exchange Symbol Series/Expiry Open High Low Prev Close LTP NSE ACC EQ NSE ADANIENT EQ NSE ADANIPORTS EQ NSE BERGEPAINT EQ NSE BHARATFORG EQ
_____ Workbook: AlertTestData.xlsx ( Using Excel 2007 32 bit )
Worksheet: Alert.
Row\Col NSE A NSE A NSE A NSE A NSE A NSE A NSE A NSE A NSE A NSE A NSE A
New macro here: http://www.excelfox.com/forum/showth...ll=1#post13145
After running new macro
_____ Workbook: AlertTestData.xlsx ( Using Excel 2007 32 bit )
Worksheet: Alert.
Row\Col NSE > A NSE A NSE > A NSE < A NSE A NSE A NSE A NSE A NSE A NSE > A NSE > A
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
Code: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
Try
Code: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
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
I do not understand too much about how to handle .csv files in Excel
this will prevent the display alert
But it may not give the results you want.Code:Wb2.Save
Let Application.DisplayAlerts = False
Wb2.Close
Let Application.DisplayAlerts = True
Wb1.Close
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.
This worked for me Doc SirCode:Wb2.Save
Let Application.DisplayAlerts = False
Wb2.Close
Let Application.DisplayAlerts = True
Wb1.Close
Thnx Alot Doc Sir
Probelm Solved
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.,
Minor changes are there in this post
Code: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.xlsx")
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
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
Code: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
Code: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
Problem Solved
Thnx Doc Sir for helping me in solving this problem Sir
Have a Great Day Sir
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/show...dition-matches
Download a csv file from below link
https://drive.google.com/file/d/1Ec2...ew?usp=sharing
cross post .. another !!!!
https://www.excelforum.com/excel-pro...ml#post5342868
I think you may have screwed up again and once again given the wrong explanation….
… I think you mean…… Match Column I of sample1.xls with column B (Field 2) of sample2.csvQuote:
Originally Posted by fixer
Field 2(column B) values in your Sample2.csv comma separated values text file:
NSE , , , < , , A , , , , , GTT NSE , , , < , , A , , , , , GTT NSE , , , < , , A , , , , , GTT
Column I of Sample1.xls
Row\Col
Here is your supplied text file, Sample2.csv:
https://excelfox.com/forum/showthrea...ll=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 )
Worksheet: Sample1 5June
Row\Col Exchange Symbol Series/Expiry Open High Low Prev Close LTP NSE ACC EQ SHORT NSE ADANIENT EQ SHORT NSE ADANIPORTS EQ BUY NSE ADANIPOWER EQ SHORT NSE AMARAJABAT EQ SHORT
Sample2.csv
https://excelfox.com/forum/showthrea...ll=1#post13475
https://excelfox.com/forum/showthrea...ll=1#post13476
After: running macro here: https://excelfox.com/forum/showthrea...ll=1#post13478
Sample2After.csv
https://excelfox.com/forum/showthrea...ll=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
Path is different, It is not in the same path when i put thisCode:Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "Sample2.csv"
I got errorCode:Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample2.csv")
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/showthrea...ll=1#post13433
Same answer as here… https://excelfox.com/forum/showthrea...iles#post13493
….etc… etc….Quote:
Originally Posted by DocAElstein
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/showthrea...ll=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/fe...2/#post-264364 , does not try to open a text file with Excel
I do not try here http://www.eileenslounge.com/viewtop...269105#p269105 at Eileen’s Lounge to open a text file with Excel
jindon at excelforum here https://www.excelforum.com/excel-pro...ml#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_...le_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/showthrea...ll=1#post13431
Also, look at the end of the macro:
If you want to open the text file with Excel then do like this:Code:'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:Code: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
)Code: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
Question:Code: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
Answer: ' CHANGE TO SUITQuote:
Originally Posted by fixer
( Forget this .._Code:Let PathAndFileName = "C:\Users\WolfieeeStyle\Desktop\sample2.csv"
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
Getting Error
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) )
I already uploaded all the files& I am runing the macro on the same file which I uploaded here
I re downloaded, and re ran the macro (Sub VBAAppendDataToTextFileLineBasedOnTheTextFileAndEx celFileConditions() : https://excelfox.com/forum/showthrea...ll=1#post13478 )
This is the file I used: https://app.box.com/s/0ej2h41g9fvm94cflf2j60a8o6p3334t , downloaded from post #1 , https://excelfox.com/forum/showthrea...ll=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.
Yes Doc Sir, I am getting that Error Again
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
Code: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
Plz See
Please use the file I uploaded
https://excelfox.com/forum/showthrea...ll=1#post13591
and after running the macro, return the file to me
( change the path to suit in the macro in the uploaded file )Quote:
Originally Posted by DocAElstein
Plz see
You are not using the file that you uploaded in post #1
I need the file that you are using!!!
_____ Workbook: Test2csvContentsReturn.xls ( Using Excel 2007 32 bit )Quote:
Originally Posted by DocAElstein
Worksheet: Sheet1
Avinash File Yous are using Alan File You uploaded in post #1 NSE, NSE,30,6,<,12783,A,,,,,GTT NSE, NSE,101010,6,<,12783,A,,,,,GTT,,,,,,,,,,,,, NSE, NSE,28,6,<,1499,A,,,,,GTT NSE, NSE,22,6,<,12783,A,,,,,GTT,,,,,,,,,,,,, NSE, NSE,25,6,<,3282,A,,,,,GTT NSE, NSE,17388,6,<,12783,A,,,,,GTT,,,,,,,,,,,,, NSE, NSE,22,6,<,382,A,,,,,GTT ,100 ,100,,,,,,,,,,,,,,,,,,,,,, ,25, ,25,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,, ,,,,,,,,,,,,,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,,,,,,,,,
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/showthrea...ll=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:So the original error ( https://excelfox.com/forum/showthrea...ll=1#post13530 ) , probably came because you were using a different file again!!!Quote:
Originally Posted by fixer
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 )
Worksheet: Sheet1
Text file You uploaded in post #15 text file You were using post #13 Text file You uploaded in post #1 NSE,101010,6,<,12783,A,,,,,GTT NSE,30,6,<,12783,A,,,,,GTT NSE,101010,6,<,12783,A,,,,,GTT,,,,,,,,,,,,, NSE,22,6,<,12783,A,,,,,GTT NSE,28,6,<,1499,A,,,,,GTT NSE,22,6,<,12783,A,,,,,GTT,,,,,,,,,,,,, NSE,17388,6,<,12783,A,,,,,GTT NSE,25,6,<,3282,A,,,,,GTT NSE,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,,,,,,,,,
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!!
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
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.
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
: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/showthrea...ll=1#post13482
Macro https://excelfox.com/forum/showthrea...ll=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/showthrea...ll=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
Download file link
https://drive.google.com/file/d/1cpu...ew?usp=sharing
https://drive.google.com/file/d/1MBR...ew?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:Code:Line Input #FileNum, TextFileLineIn ' next line in text file
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
This is the file that you uploaded : https://imgur.com/Ooc7WBU
( https://drive.google.com/file/d/1MBR...ew?usp=sharing )
It is the same text file that you gave in post#1
https://excelfox.com/forum/showthrea...ll=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..... )
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
You are talking rubbish againQuote:
Originally Posted by fixer
There are no such things as notepad files. I told you this hundreds of times
I think your problem is very very simple
I just need the text file, the sample2.csv, that gives you that error
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
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