Skip to Content
0
Former Member
Feb 20, 2006 at 04:27 PM

BEx and VBA

38 Views

I have created a worksheet with several views of the same query inserted in it. Each query view contains the stock quantity and value of a different product brand.

For each query I would like to calc. the movements(quantity and value) and place them in the next two columns after the result area. Here is what I have done so far.

Sub SAPBEXonRefresh(queryID As String, ResultArea As Range)

Select Case queryID

Case "SAPBEXq0008"

StockValues ResultArea

Case "SAPBEXq0009"

StockValues ResultArea

Case "SAPBEXq00010"

StockValues ResultArea

Case "SAPBEXq00011"

StockValues ResultArea

Case "SAPBEXq00012"

StockValues ResultArea

Case "SAPBEXq00014"

StockValues ResultArea

End Select

End Sub

Sub StockValues(ByRef ResultArea As Range)

Dim lngVar As Long

For lngVar = 1 To ResultArea.Rows.Count - 1

If ResultArea.Cells(lngVar, 2) = "" Then

SetMovementsPost lngVar, ResultArea.Columns.Count, ResultArea

SetMovementsPre lngVar, ResultArea.Rows.Count - 1, ResultArea.Columns.Count, ResultArea

End If

Next

End Sub

Sub SetMovementsPost(vlngvar As Long, vlngColumns As Long, ByRef ResultArea As Range)

Dim lngLoop As Long

Dim lngStart As Long

Worksheets("BW Stock").Select

ResultArea.Cells(vlngvar, vlngColumns).Activate

ActiveCell.Offset(0, 1).Value = ResultArea.Cells(vlngvar, 3)

ActiveCell.Offset(0, 2).Value = ResultArea.Cells(vlngvar, 4)

lngStart = vlngvar - 1

If lngStart > 2 Then

For lngLoop = lngStart To 2 Step -1

ResultArea.Cells(lngLoop, vlngColumns).Activate

ActiveCell.Offset(0, 1).Value = ResultArea.Cells(lngLoop - 1, 3) + ResultArea.Cells(lngLoop, 3)

ActiveCell.Offset(0, 2).Value = ResultArea.Cells(lngLoop - 1, 4) + ResultArea.Cells(lngLoop, 4)

Next

End If

End Sub

Sub SetMovementsPre(vlngvar As Long, vlngRows As Long, vlngColumns As Long, ByRef ResultArea As Range)

Dim lngLoop As Long

Dim lngStart As Long

lngStart = vlngvar + 1

For lngLoop = lngStart To vlngRows

ResultArea.Cells(lngLoop, vlngColumns).Activate

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value - ResultArea.Cells(lngLoop, 3)

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ActiveCell.Offset(-1, 0).Value - ResultArea.Cells(lngLoop, 4)

Next

End Sub

I was under the impression that query ID's will identify each queries result area uniquely and just re-use Sub StockValues however, I can not get the VB code to display the actual movement qty/amounts.

How do I uniquely identify each queries result area or is there another way of achieving this.

PS. I am not a VB expert so please be a bit explicit in your response.

Thanks,

Milind