-
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 )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
|---|
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
_____ Workbook: AlertTestData.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
|---|
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 |
|
|
|
|
|
|
|
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 |
G |
|---|
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 |
|
|
|
|
|
|
|
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
-
1 Attachment(s)
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
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.
-
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
-
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
