Problem in Workbook

[Former Member]

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.

[Former Member]

no solution

[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.

[Former Member]

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

[Former Member]

Hi Bob,

Thanks for your help. However, since i am new to VB and WB, it would be great if you can provide a bit more assistance on the same.

Regards

Snehith.