cancel
Showing results for 
Search instead for 
Did you mean: 

EPM 10 VB Code for deriving values after division

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (2)

Answers (2)

cecilia_petersson2
Active Participant
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Where is the AFTER_REFRESH event?????

You are using the AFTER_WORKBOOK_OPEN event and then refreshing the sheets. Why the need to refresh the sheets in the macro? Do you want to execute the code after opening the workbook?

Regards,

JP

Former Member
0 Kudos

Hi Jp,

Also I tried below code but not working

Sub AFTER_REFRESH()

ActiveWorkbook.RefreshAll

'

'

Range("D117").Value = Range("D117").Value / 100000

Range("E117").Value = Range("E117").Value / 100000

End Sub

Regards,

Foram

Former Member
0 Kudos

Hi Jp,

Also I tried below code but not working

Sub AFTER_REFRESH()

ActiveWorkbook.RefreshAll

'

'

Range("D117").Value = Range("D117").Value / 100000

Range("E117").Value = Range("E117").Value / 100000

End Sub

Regards,

Foram

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Jp,

Also find below screenshots highlighted values in orange it might give you some Idea on my requirement and problem I am facing.

Regards,

Foram

Former Member
0 Kudos

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

Former Member
0 Kudos

Hello Foram,

In your VB code it should be Function AFTER_REFRESH() as it is a function.

Thanks!!

Former Member
0 Kudos

Hi PT,

Can you elaborate in my context.

Below is the code can you edit and let me know, I will try the code given by you.

Sub AFTER_REFRESH()

'

'

Range("D117").Value = Range("D117").Value / 100000

Range("E117").Value = Range("E117").Value / 100000

End Sub

Regards,

Foram

Former Member
0 Kudos

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

Former Member
0 Kudos

Hello Foram,

Function AFTER_REFRESH()

'

'

Range("D117").Value = Range("D117").Value / 100000

Range("E117").Value = Range("E117").Value / 100000

End Function


Thanks!!

Former Member
0 Kudos

Hi PT,

Even the above mentioned code by you is not working, Whenever i refresh the report I do not get updated value instead I am getting the same value.

Is there any other alternative?

Regards,

Foram

Former Member
0 Kudos

Hi Foram,

Have you tried debugging the macro code? The above code should work fine......Try copying it in Module instead of Sheet...

Other option would be to use conditional formatting of Excel.......Refer to Vadim's reply in this thread..

Regards,

JP

Former Member
0 Kudos

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.