You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Extraction of data using raw report – VBA help needed
- Thread starter Anand307
- Start date
1.What is You challenge with that? Iam getting an run time error is Run – time error-‘2147417848(80010108)’: Method ‘Cells’ of object’_worksheet’ failed. So I started using the other code for Private Sub Worksheet_Change(ByVal Target As Range) mentioned above. Upon end the code I receive Not enough system recourses to display completely.
2.What should it do? Could you please add the additional feature in
If .Cells(ay, ax) = "MTM" Then
.Cells(ay, 9) = .Cells(ay, 7) + .Cells(ay, 8)
Else
.Cells(ay, 9) = .Cells(ay, 7) - .Cells(ay, 8)
In the code that I provided above please.
3.Have You tested it?Yes
4.What should change? Had an idea to add additional check - If in column J the comment is going to start as MTM break or Known MTM break, IA break or known IA break, Notional Break or Known Notional Break, Unmatched break.
Scenario 1 – after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known Notional break” or “MTM break” however I go ahead and select MTM from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Here I will make sure all the comments begins with the name of break type example use raw file attached. On break report row 2, Comment would come to Column J starting with “Notional Break - As CS has confirmed that the trades with # are pending settlement trades and will be off the portfolio post final settlement, requested for the settlement date” And in case by mistake I select as MTM from Column F drop down then we need a pop saying incorrect selection. This step is just to ensure we are flagging correct break type in column F and the corresponding comments are pulled correct.
2.What should it do? Could you please add the additional feature in
If .Cells(ay, ax) = "MTM" Then
.Cells(ay, 9) = .Cells(ay, 7) + .Cells(ay, 8)
Else
.Cells(ay, 9) = .Cells(ay, 7) - .Cells(ay, 8)
In the code that I provided above please.
3.Have You tested it?Yes
4.What should change? Had an idea to add additional check - If in column J the comment is going to start as MTM break or Known MTM break, IA break or known IA break, Notional Break or Known Notional Break, Unmatched break.
Scenario 1 – after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known Notional break” or “MTM break” however I go ahead and select MTM from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Here I will make sure all the comments begins with the name of break type example use raw file attached. On break report row 2, Comment would come to Column J starting with “Notional Break - As CS has confirmed that the trades with # are pending settlement trades and will be off the portfolio post final settlement, requested for the settlement date” And in case by mistake I select as MTM from Column F drop down then we need a pop saying incorrect selection. This step is just to ensure we are flagging correct break type in column F and the corresponding comments are pulled correct.
This looks good, just worked on scenarios and have put all the scenarios together(Amendment made in Scenario1 provided earlier)
Scenario 1 – after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known Notional break” or “Notional break” however I go ahead and select MTM/IA from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Scenario 2 - after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known MTM break” or “MTM break” however I go ahead and select Notional/IA from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Scenario 3 - after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known IA break” or “IA break” however I go ahead and select MTM/Notional from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Suggestion- it would be easier to put it in this way, If .Cells(ay, ax) = "MTM" Then match with ay10 with in left of 20 for the name "MTM".
Similarly with Notional - If .Cells(ay, ax) = "Notional" Then match with ay10 with in left of 20 for the name "Notional".
Similarly with Notional - If .Cells(ay, ax) = "IA" Then match with ay10 with in left of 20 for the name "IA".
If there is no match to drop down selection in Column J, then throw up the error msg.
Scenario 1 – after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known Notional break” or “Notional break” however I go ahead and select MTM/IA from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Scenario 2 - after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known MTM break” or “MTM break” however I go ahead and select Notional/IA from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Scenario 3 - after hitting the macro button and I go ahead and manually select break type from column F. Assume the comment has started with “Known IA break” or “IA break” however I go ahead and select MTM/Notional from drop down which is incorrect- at this point of time please code that will need to show a pop up indicating our selection from down is incorrect.
Suggestion- it would be easier to put it in this way, If .Cells(ay, ax) = "MTM" Then match with ay10 with in left of 20 for the name "MTM".
Similarly with Notional - If .Cells(ay, ax) = "Notional" Then match with ay10 with in left of 20 for the name "Notional".
Similarly with Notional - If .Cells(ay, ax) = "IA" Then match with ay10 with in left of 20 for the name "IA".
If there is no match to drop down selection in Column J, then throw up the error msg.
Below are the findings -
Before selecting anything in column F drop down Iam getting a error msg, which is incorrect. Only after completion of selection from Column F drop down if the selection does not match with in first few character of comment then we need to have this error msg.
Then if we go with the example in row one, considering you are using the latest raw report - After running the macro for row2, I came to Column F and selected as Notional from the drop down , however iam getting error msg as "is incorrect break type" where if you see in comments column J the comment has started with "Notional Break", hence i should not have got the error msg as the selection matches the first few charecter of comment. In case instead of selecting notional, if i had selected MTM from the drop down then I need to get the error as my selection will not match the first few charecters of comments
I have re attached the raw report for your reference
Before selecting anything in column F drop down Iam getting a error msg, which is incorrect. Only after completion of selection from Column F drop down if the selection does not match with in first few character of comment then we need to have this error msg.
Then if we go with the example in row one, considering you are using the latest raw report - After running the macro for row2, I came to Column F and selected as Notional from the drop down , however iam getting error msg as "is incorrect break type" where if you see in comments column J the comment has started with "Notional Break", hence i should not have got the error msg as the selection matches the first few charecter of comment. In case instead of selecting notional, if i had selected MTM from the drop down then I need to get the error as my selection will not match the first few charecters of comments
I have re attached the raw report for your reference
Attachments
@Anand307
1) The 1st message comes because there are EnableEvents = True,
that gotta modify, then run that Macro.
2) You gave a list of trigger words, Okay? (#31 Reply)
As I tried to tell already with words 'MTM' is different than 'MtM' (#32 Reply).
"Notional Break" is different than "Notional break" and so on...
I even checked You 'Raw report'-file ... there is text like 'Known MTM Break ...', it's not in 'a list of trigger words'!
3) I understood that You want message IF some combination of selection and trigger words match. Previous means for me that normally no message!
And You wrote (#27 Reply Here I will make sure all the comments begins with the name of break type example use raw file attached. ) this.
>> If You need to change Your 'trigger word list' it is possible to...
1) The 1st message comes because there are EnableEvents = True,
that gotta modify, then run that Macro.
2) You gave a list of trigger words, Okay? (#31 Reply)
As I tried to tell already with words 'MTM' is different than 'MtM' (#32 Reply).
"Notional Break" is different than "Notional break" and so on...
I even checked You 'Raw report'-file ... there is text like 'Known MTM Break ...', it's not in 'a list of trigger words'!
3) I understood that You want message IF some combination of selection and trigger words match. Previous means for me that normally no message!
And You wrote (#27 Reply Here I will make sure all the comments begins with the name of break type example use raw file attached. ) this.
>> If You need to change Your 'trigger word list' it is possible to...
Attachments
I did test - Below are the findings –
I see that in break report row 2 the comment is starting with “Notional Break”, however when I select Notional from column F drop down, it still gives error msg, where in it should not give the error as I am selecting the correct item from drop down. The check is basically to ensure that the comment and drop down selection should be inline( should mean the same). I tried to take care of changing trigger words list as “ Notional Break” however it is still not picking correct.
We need error msg only when the selection from drop down does not match the first few character of comments in J column
I see that in break report row 2 the comment is starting with “Notional Break”, however when I select Notional from column F drop down, it still gives error msg, where in it should not give the error as I am selecting the correct item from drop down. The check is basically to ensure that the comment and drop down selection should be inline( should mean the same). I tried to take care of changing trigger words list as “ Notional Break” however it is still not picking correct.
We need error msg only when the selection from drop down does not match the first few character of comments in J column