Skip to Content
author's profile photo Former Member
Former Member

Problem in Workbook

Hi all,

I have the following scenario in my worksheet.

Row1

Row2

Row3

Total1

Row4

Row5

.

.

.

Rown

Total2

Total3=Total1+Total2.

The thing here is that Row1,Row2 and Row3 come from one query and Row4, Row5,...Rown come from another query ,Also, the number of rows in the second query are based on a condition , so there is a possibility where in i can get no results based on the condition as well..

My question is, is there any possibility to shift the total3 row dynamically upwards or downwards based on the number of rows in query2 ?? Please let me know if there is any option in excel or any script for the same.

Thanks and Regards

snehith.

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 05, 2009 at 01:49 PM

    Snehith,

    You can use something like the following command in Visual Basic to get the range of the report data of the second query and place it in the variable RptRng:

    Set RptRng = ActiveCell.CurrentRegion

    You can find more details on the SAP delivered enhancements to VBA in the on-line SAP Help pages.

    Hope that is enough to get you started...

    Bob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 07, 2009 at 11:36 AM

    no solution

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      What you need to do is add code in Excel. In Excel click ALT + F11 to open VBA Editor.

      Then if you see a Module section there should be SAPBEX ( in 3.x). If not you can always add a module and name it What ever you want.

      The SAPBEX should have the following in there

      Sub SAPBEXonRefresh(queryID As String, resultArea As Range)
      
      End Sub
      

      If not again you can paste the above lines.

      Now in this procedure you can add your code.

      Sub SAPBEXonRefresh(queryID As String, resultArea As Range)
          Dim lRow As Long
          Dim sAddress As String
          
          sAddress = resultArea.Address 
      
          lrow = 14  'Default Row if there is no Data returned. 
      
          If Right$(queryID, 4) = "0002" Then ' Check if the Second Query has been refreshed. 
              If InStr(sAddress, ":") > 0 Then ' If the Address has more than 1 Row 
                  lRow = Right$(sAddress, Len(sAddress) - InStrRev(sAddress, "$"))   'Get the Last Row from the Range. 
              End If
          End If
              
          MsgBox lRow      'Show the Last Row from Second Query. 
                  
      End Sub
      

      Here I have displayed where the last row of my Second Query is. What you can do is add some rows to the counter and use it for your total Line.

      Hope this helps.

      Datta.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.