In support of this post
http://www.excelfox.com/forum/showth...ll=1#post13140

Explanation of the problem
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:
Consider the results of this test data

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
Worksheet: 1-Sheet1 27Apr_2 (2)

_____ Workbook: AlertTestData.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
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.


results After
_____ Workbook: AlertTestData.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
1
 NSE
25
6
 A
2
 NSE
17388
6
 A
3
 NSE
404
6
 A
4
 NSE
422
6
 >
116815
 A
5
 NSE
10604
6
 A
6
 NSE
438
6
 A
7
 NSE
10794
6
 A
8
 NSE
1250
6
 >
13595
 A
9
 NSE
625
6
 A
10
 NSE
15083
6
 >
27335
 A
11
 NSE
22
6
 A
12
Worksheet: Alert.


Those results come from using this macro here: http://www.excelfox.com/forum/showth...ll=1#post13143
Those results arise due to this problem code line
Ws2.Columns(2).Find(.Cells(i, 9))

That code line is only using one argument for Range.Find Method
So VBA must guess the others. It has guessed not what we want. It has guessed similar to this
Ws2.Columns(2).Find(What:=.Cells(i, 9), After:=B1, LookAt:xlpart)

Because of After:=B1 , it starts to look from B2 in
Because of LookAt:xlpart , we will look for what we want anywhere inside a cell, so if we are looking for the number 25 , then all these numbers or even text could be a match
4567256
2500
25
564rghsseeffzz25adksfhaejh
VBA will choose the first match that it finds

For example, for our 25 it started looking from B2 in Worksheet Alert, and the first it found was 1250


For large data, there will be many errors caused by this problem. But the problem and the solution will be the same.
It is easier to demonstrate the problem with small test data.
It is easier to test a solution with small test data.

It is the responsibility of the person finally responsible for the macros in real use to take the time to check for larger amounts of real data. For getting free help in a forum , this will be the responsibility of the persom getting help.


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