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

**fixer** · 04-27-2020, 01:08 AM

No Problem Doc Sir Take ur Time
Have a Great Day

**DocAElstein** · 04-27-2020, 02:50 PM

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

**DocAElstein** · 04-27-2020, 05:00 PM

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

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	L
1	Exchange	Symbol	Series/Expiry	Open	High	Low	Prev Close	LTP
2	NSE	ACC	EQ	1182	1193	1151.7	1190.45	1156.6	22	11.566	116815	1168.166
3	NSE	ADANIENT	EQ	137.15	140.55	134.1	140.5	134.65	25	1.3465	13595	135.9965
4	NSE	ADANIPORTS	EQ	273.95	276.95	269.55	277.6	270.65	15083	2.7065	27335	273.3565
5	NSE	BERGEPAINT	EQ	521	535	515	519.7	519.7	404	5.197	52485	524.897
6	NSE	BHARATFORG	EQ	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	F
1	NSE	25	6	A
2	NSE	17388	6	A
3	NSE	404	6	A
4	NSE	422	6	A
5	NSE	10604	6	A
6	NSE	438	6	A
7	NSE	10794	6	A
8	NSE	1250	6	A
9	NSE	625	6	A
10	NSE	15083	6	A
11	NSE	22	6	A
12

Worksheet: Alert.

New macro here: http://www.excelfox.com/forum/showth...ll=1#post13145
After running new macro

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

_Row\^Col	A	B	C	D	E	F
1	NSE	25	6	>	13595	A
2	NSE	17388	6			A
3	NSE	404	6	>	52485	A
4	NSE	422	6	<	26065	A
5	NSE	10604	6			A
6	NSE	438	6			A
7	NSE	10794	6			A
8	NSE	1250	6			A
9	NSE	625	6			A
10	NSE	15083	6	>	27335	A
11	NSE	22	6	>	116815	A
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** · 04-27-2020, 05:43 PM

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

**DocAElstein** · 04-27-2020, 05:50 PM

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

**fixer** · 04-27-2020, 05:58 PM

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** · 04-27-2020, 06:24 PM

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

this will prevent the display alert

Code:

 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** · 04-27-2020, 06:33 PM

Code:

 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** · 04-27-2020, 06:37 PM

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** · 04-27-2020, 06:38 PM

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