on 08-03-2016 11:01 AM
Hello Expert,
I have a requirement of dividing certain values by 100000, For this I have used various types of formulas and number format but it is not working out.
So after making certain research I came with a conclusion that we can achieve the same by writing a VB Code in a BPC Workbook via Macros, So I Wrote the same and here is the example of my code.
Sub Workbook_RefreshAll()
ActiveWorkbook.RefreshAll
'
'
Range("D117").Value = Range("D117").Value / 100000
End Sub
Basically I want the value in cell D117 on one worksheet to be divided by 100000, I am getting these properly but when I refresh again by going to EPM Tab above the calculated value derived by VB Code disappear and I am getting value which is not divided by 100000.
Below attached is the screen from where I am refreshing my BPC Workbook.
Please let me know in-case of any other solution on these or if I am missing out any steps.
Appreciate your efforts in advance.
Thanks & Regards,
Foram
Hi Foram,
As I don't have the full scenario I'm not sure if this would work, but... I assume you have a value of, say, 1,000,000 in the database on account ABC, and you want it to display as 10. How about adding account ABCX, setting the dimension formula to [ABC]/100,000, and displaying this account instead?
/Cecilia
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Foram,
After Refresh, the formatting sheet is getting applied because of which your formula applied by macro must be getting override!. Use AFTER_REFRESH event then do your calculations in the macro code..
Custom VBA Functions (Events) - What's New - SAP Library
Hope this helps...
Regards,
JP
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jp,
Thank for your reply, But even after using after refresh still I am not getting the desired results
Below mentioned is the code, Correct me if I am wrong
Sub AFTER_WORKBOOK_OPEN()
ActiveWorkbook.RefreshAll
'
'
Range("D117").Value = Range("D117").Value / 100000
Range("E117").Value = Range("E117").Value / 100000
End Sub
Regards,
Foram
Try with this code.....Place this code in Sheet....not module! I dont have any system with me...so try with you.
From the User options keep the "Refresh the whole workbook while opening"
Option Explicit
Sub AFTER_REFRESH()
'
'
Range("D117").Value = Range("D117").Value / 100000
Range("E117").Value = Range("E117").Value / 100000
End Sub
Regards,
JP
Hi JP,
The code given by you did not worked out, Any other option and yes I kept option "Refresh the whole workbook while opening" but no results.
Do we have any specific code which may be used for refreshing value while using EPM or other options in codes through which we can make refreshing values possible.
Appreciate your help and please let me know if any other solution on the same.
Regards,
Foram
Have you read the link posted by me earlier???
sample:
Option Explicit
Function AFTER_REFRESH()
MsgBox "Refresh successful"
AFTER_REFRESH = True
End Function
Its should be Function instead of Sub!
If it still gives a problem after the above correction....then try to debug the macro code....
Regards,
JP
HI Foram,
seems above code is correct only. You have added code in Sheet instead of that add code in module.But still follow procedure.
Function after_refresh()
Range("i9").Value = Range("A9").Value / 100000
End Function
Below is the result. In that I have mentioned formulas also for just validation purpose.
Hope now you are clear and this will solve your problem also. Still if you are facing problem then Please share screen shots. code within the module.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.