The biggest problem is..
Since a few weeks you have introduced a new problem for yourself which will help to ensure that you fail in everything: You make many cross post and everyone helping you writes there codes slightly differently. So you are getting into a mixed up chaotic mess of different codlings and are beginning to post many wrong files and incorrect or incomplete macros and explanations of what you want.
You are getting into a mixed up mess
I seem to be answering almost the same question over and over again. But it gets harder because you are getting into a mixed up mess and are getting worse and worse at explaining what you want.
Cross posting bad explanations just makes everything worse. It makes it harder for anyone to help you, and more and more people will get fed up with you and stop helping you
You think you are clever by secretly finding new places and registering new account usernames to cross post the same or similar question. But it is just creating a total chaos and you are losing control yourself and mixing up macros and files everywhere.
You can ask the wrong question in a thousand places and still get the wrong answer a thousand times. ( By luck, you might once get the right answer, but you probably would never notice )
The macro you have posted https://excelfox.com/forum/showthrea...ll=1#post14591 does the following:
It looks for the data values in column I of Ws1 in column B of Ws2. So the range to be searched is column B in Ws2. The values to be searched for are in column I of Ws1
When a matched value is found we look at the value in column D of Ws2 at the row where the match occurred. Based on whether or not we have “>” in that cell we will add or subtract 1% to the value in E in Ws2 at the row being considered, that is given by loop variable, i
This last bit is not what you want
For example, first we look for 22 (from row I=2) in column B of Ws2. That is found in row 1 of Ws2.
So
R2=WorksheetFunction.Match(.Cells(i, "I"), Ws2.[B:B], 0)=1
In row 2 of Ws2 in the D column is a < so we take the option of
.Cells(i, "K").Value = Ws2.Cells(i, "E").Value + 0.01 * Ws2.Cells(i, "E").Value
That code line takes the value in column E at row 2 of Ws1 , which is 200 and adds 1% which is 2, giving you 202, which is not what you want.
You are pasting in the correct file and correct row. But you are taking the data from the correct file but the wrong row
What you want it to take the value at the row where the match was found in Ws1, which is 1
That will give the output of 100 +1 = 101
The macro does what you asked for:
If column I of 1.xls matches with column B of Alertcodes.xlsx & column D has < this then calculate the 1% of of column E & add that 1% to column E & the result which will come it will be pasted to column K of 1.xls
If column I of 1.xls matches with column B of Alertcodes.xlsx & column D has > this then calculate the 1% of of column E & subtract that 1% to column E & the result which will come it will be pasted to column K of 1.xls
But what you asked for could mean many things and have many different answers. Your explanation was incomplete. You explanation was very bad.
This was error
Ws2.Cells(i, "E").Value - 0.01 * Ws2.Cells(i, "E").Value
This is correction
Ws2.Cells(R2, "E").Value - 0.01 * Ws2.Cells(R2, "E").Value
We should be doing the calculations to get the result from the matched row in AlertCodes.xlsx, ( Ws2) , which is given by R2, not i
This is what you want:
Question:
We consider the data values in column I of 1.xls, starting from row 2. Values in column I of 1.xls, starting at row 2, are to be looked for, ( Matched ) in column B of AlertCodes.xlsx
At the row in AlertCodes.xlsx where the match is found, the matched row, the following is to be done:
Consider the value in column D of AlertCodes.xlsx at the matched row in AlertCodes.xlsx
& If column D of AlertCodes.xlsx has this, < , then calculate the 1% of column E of AlertCodes.xlsx & add that 1% to column E of AlertCodes.xlsx in that match row & the result which will come it will be pasted to column K of 1.xls at the row of the considered data value in 1.xls
Or
else if column D of AlertCodes.xlsx has this, > , then calculate the 1% of column E of AlertCodes.xlsx & subtract that 1% from column E of AlertCodes.xlsx in the match row & the result which will come it will be pasted to column K of 1.xls at the row of the considered data value in 1.xls
Solution:
Here https://excelfox.com/forum/showthrea...ll=1#post14594
Before:
_____ Workbook: AlertCodes.xlsx ( Using Excel 2007 32 bit )
Worksheet: Sheet4 July 13 2020
Row\Col A B C D E F G H I J K 1 NSE 22 6 < 100 A GTT 2 NSE 25 6 < 200 A GTT 3 NSE 15083 6 < 300 A GTT
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Worksheet: 1-Sheet1 13July
Row\Col A B C D E F G H I J K 1 Exchange Symbol Series/Expiry Open High Low Prev Close LTP 2 NSE ACC EQ 1265 1282.7 1246.5 1275.3 1247 22 BUY 3 NSE ADANIENT EQ 151.85 165.45 151.4 151.85 152.35 25 BUY 4 NSE ADANIPORTS EQ 348 348 338.5 346.55 338.85 15083 BUY
After running macro here https://excelfox.com/forum/showthrea...ll=1#post14594
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Worksheet: 1-Sheet1 13July
Row\Col A B C D E F G H I J K 1 Exchange Symbol Series/Expiry Open High Low Prev Close LTP 2 NSE ACC EQ 1265 1282.7 1246.5 1275.3 1247 22 BUY 101 3 NSE ADANIENT EQ 151.85 165.45 151.4 151.85 152.35 25 BUY 202 4 NSE ADANIPORTS EQ 348 348 338.5 346.55 338.85 15083 BUY 303
Alan
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
AlertCodes.xlsx : https://app.box.com/s/jwpjjut9wt3ej7dbns3269ftlpdr7xsm
macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
Corrected macro, Sub STEP6() : https://excelfox.com/forum/showthrea...ll=1#post14594
Reply With Quote
Bookmarks