cancel
Showing results for 
Search instead for 
Did you mean: 

Possible solution??

Former Member
0 Kudos

I have a workbook which runs of multiprovider with Purchasing data wherein i need a to calculate the result for every row based on simple formula. But the overall result overides and has a slightly different calculation.

My question is can i create a VBA macro with a button which will change all the result rows only with a click.

Accepted Solutions (1)

Accepted Solutions (1)

former_member188975
Active Contributor
0 Kudos

Hi Kenneth,

That would be possible...I once attempted something similar and used the background color of the result cell to distinguish between a normal row and result row...may be not so sophisticated, but it worked for our requirement.

Hope this helps...

Former Member
0 Kudos

Thanx for the quick answer. Can you gimme that part of the code if you still have it.

It would be a big favor.

Former Member
0 Kudos

Bhanu, Do you think by using the color coding would not only change the value in 1 cell but all the cells that are yellow. Can you show me the piece of code?

Greatly appreciate your help.

Former Member
0 Kudos

Hi,

If you are getting the result of formul at overall result row is different to the summation of all rows values of this formula, you can get correct answer with the help of a 'How to ....' document.

Let me know if I understood your problem properly.

With rgds,

Anil Kumar Sharma .P

Former Member
0 Kudos

Yes, you understood right! Can you telling me which document you are refering to??

Appreciate your help here.

Former Member
0 Kudos

Hi,

Try to do some exercise by chaniging the formula's property 'formula collision'.I hope your problem will be solved here it self.

http://help.sap.com/saphelp_bw32/helpdata/en/d2/02223c5f00612be10000000a11402f/frameset.htm

With rgds,

Anil Kumar Sharma .P

(Kindly assign points to the responces)

Message was edited by: Anil Kumar Sharma

Message was edited by: Anil Kumar Sharma

Former Member
0 Kudos

I have already tried it before. It dosent solve. Can you suggest any other ways. Thanx for your effort.

Former Member
0 Kudos

Hi,

Let me know the exact problem with an example(sample records).

And

R you getting this problem only at drill up of characterstics?

With rgds,

Anil Kumar Sharma .P

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Kenneth,

When you determined first and last rows where you want to make a search you can do something like following (in the sub related to button click).

Private Sub CommandButton_Click()

Dim I As Integer, YourCoulumn As Integer

Dim Worksheet1 As Worksheet

Dim rng As String

Set Worksheet1 = ThisWorkbook.Worksheets("SHEET1")

For I = FirstRow To LastRow

rng = GetRange(YourCoulumn, I)

If Worksheet1.Range(rng).Interior.ColorIndex = 36 Then

  • put here your logic

End If

Next

End Sub

Function GetRange(ByVal col As Integer, ByVal row As Integer) As String

Dim RR As String

RR = Chr(col + Asc("A") - 1)

GetRange = Trim(RR) + Trim(Str(row))

End Function

How to determine rows and work with result area you can find here:

Best regards,

Eugene

Message was edited by: Eugene Khusainov

Former Member
0 Kudos

Thats was very informative Eugene. I will try and get back to you. Thank you.

Former Member
0 Kudos

Ashish, Yes it contains drill down chars. And its doing the calcualtion correctly but only diff is that i want a diff calcualtion at result row level.

Eugene, I dont know the column as it changes as drill down chars are added. so how to you change only the Result row calculation and irrespective of the column keep other calcualtions unaltered.

Thanx people. Help wanted as I am taking this as a challenge.

former_member188975
Active Contributor
0 Kudos

Hi Kenneth,

You could put in a check to find the last column with values in the cells, as any columns after the one with the result would be blank (assuming that results are displayed on the right)...

Former Member
0 Kudos

<b><i>so how to you change only the Result row calculation and irrespective of the column keep other calcualtions unaltered.</i></b>

I have a document on this which was given to me by Anil..I can forward that to you..May be it might help you..

Its titled

"HowtoExceptionalAggregationForCalculatedKeyfigure.doc"

Can you give me your E-mail address..

I will forward it to you..

Ashish..

Former Member
0 Kudos

Bhanu, I didnt quite understand what your were suggesting, can you be more elaborate.

Appreciate your input here.

Former Member
0 Kudos

Ashish, If its there in the HowTo..Guides in Serivce.SAP i wil be able to find it.

Thank you.

But my email is kenneth.bw@gmail.com

Former Member
0 Kudos

Hi Kenneth ,

Not sure if its available on the site..thats why had to get it thru Anil..

I have forwarded the same to you..

Ashish..

Former Member
0 Kudos

That was very nice. I will try and see if this would solve my issue. Thank you once again Ashish, it was valuable. I will keep ya posted.

Former Member
0 Kudos

Did not solve the issue. Thought "Replacement path" would have a solution but didnt. Let me know if theres any other way but will keep trying Bhanu's way.

Appreciate your help here people.

Former Member
0 Kudos

Hi Kenneth,

Sorry that I missed this last week. I also apologize for not reading all of the links before responding. So, I am not sure if what I am going to write is new or not. But, thought it might be worth trying.

So ... you can tell a result cell from other cells by the name of the "style" assigned to that cell.

If you are not already familiar with these, you can find the names of the styles BW uses in one of two ways:

1. launch the file sapbexs.xla (s for style?); you will find this in the same folder that contains your regular front end ... sapbex.xla.

2. customize one of your toolbars in Excel; add the style drop-down box to one of your Excel toolbars, then click around in any workbook containing a BW query

So, then, in your VB macro, all you have to do is (for example):

If Cell(i, j).Style = "SAPBEXaggData" then ...

- Pete