VBA Code to expand a Hierachry

[Former Member]

I have a workbook in BW (3.0), and am trying to do some formatting to the results area. I want to create a macro to expand the hierarchy after getting the results of the query. I found the following code in BW Expert Online, but I an getting an error on the underlined part of the code. I am a novice at BW, and my only exposure to VBA has been in the past month trying to format the report. Any help would be greatly appreciated.

Scott

Sub test()

'

' test Macro

' Macro recorded 4/18/2005 by Scott

'

'

Range("C19").Select

Selection.Copy

Range("E14").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Range("A1:L1").Select

SetBexCommand

End Sub

Function SetBexCommand()

Dim strCommand As String

Dim myCell As Range

strCommand = "HX03"

Set myCell = Range("D18")

<u>If Run("sapbex.xla!SAPBEXCheckCommand", strCommand, ActiveSheet.Range(strMyCell)) = 0 Then</u>

If Run("sapbex.xla!SAPBEXFireCommand", strCommand, ActiveSheet.Range(strMyCell)) = 0 Then

Else

MsgBox "Error in Hierarchy Command"

End If

End If

End Function

[Former Member]

Hi,

seems some problem with the Range property of the Worksheets object.

I would try:

If Run("sapbex.xla!SAPBEXCheckCommand", strCommand, ActiveSheet.Range("D18")) = 0 Then

If Run("sapbex.xla!SAPBEXFireCommand", strCommand, ActiveSheet.Range("D18")) = 0 Then

From the Visual Basic Help:

Range Property of Worksheet object: Range(Cell1, Cell2)

Cell1 Required Variant. The name of the range. This must be an A1-style reference in the language of the macro. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they’re ignored. You can use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the macro.

Cell2 Optional Variant. The cell in the upper-left and lower-right corner of the range. Can be a Range object that contains a single cell, an entire column, or entire row, or it can be a string that names a single cell in the language of the macro.

Heike