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

[fixer]

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

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

---

All files are located in a different path

vba code will be placed in a macro.xlsm

i have two files 1.xls & 2.csv

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

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

or

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



save and close both the file

plz help me in solving this problem by vba



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

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

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

[DocAElstein]

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

---

What does this mean?

i have a vba code but has a little error

You must give more details

---

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

Alan

[fixer]

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

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

[DocAElstein]

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


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

Or maybe this is problem in your macro …..

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

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

Alan

[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/showth...ll=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
38	NSE	CONCOR	EQ	368	376.6	359.5	367.8	361.1	4749	3.611	3647	364.711
39	NSE	CUMMINSIND	EQ	420.95	426.55	377.25	419.15	384.95	1901	3.8495	38875	388.7995
40	NSE	DABUR	EQ	499	503.75	494.5	499	499.05	772	4.9905	4941	494.0595
41	NSE	DISHTV	EQ	5.1	5.15	4.75	4.95	4.75	Empty	0.0475	475	4.7975
42	NSE	DIVISLAB	EQ	2410	2460	2390.6	2417.3	2425.4	10940	24.254	240115	2401.146
43	NSE	DLF	EQ	135	135	127.6	137	128.2	14732	1.282	12945	129.482
44	NSE	DRREDDY	EQ	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