Excel Stack & Queue VBA

[nomad815]

..........................................................................................................

 

[acw]

Hi

How about

Sub pop()
Set curSP = Range("D:D").Find(what:="SP")

Range(curSP.Address).Offset(3, 0).Value = "SP"
Range(curSP.Address).ClearContents

Range(Range(curSP.Address).Offset(1, -1), Range(curSP.Address).Offset(3, -1)).ClearContents

End Sub

for the POP code.


Tony

 

[Jaafar Tribak]

Hi There,

I tested these two procedures and worked OK :

Dim DinamicRange As Range
Dim EventNr As Long
'Dim J
Dim ItemsArr(1 To 3)
Dim StoredValue() As Variant


Sub Push()
    Set DinamicRange = Range("C2:C22")
    On Error Resume Next
    With DinamicRange
        If .SpecialCells(xlCellTypeBlanks).Count < 3 Then
            MsgBox "Stack Full", vbExclamation
            Exit Sub
        End If
        Do
            Randomize
            EventNr = Int((100 * Rnd) + 1)
            If Int(EventNr / 2) = (EventNr / 2) Then
                J = J + 1
                ItemsArr(J) = EventNr
            End If
        Loop While J < 3
        Set PopulatedCells = .SpecialCells(xlCellTypeConstants)
        If PopulatedCells Is Nothing Then
            .Cells(.Cells.Count) = ItemsArr(1)
            .Cells(.Cells.Count - 1) = ItemsArr(2)
            .Cells(.Cells.Count - 2) = ItemsArr(3)
        Else
            StoredValue = PopulatedCells.Value
            Set PopulatedCells = PopulatedCells.Offset(-3)
            PopulatedCells.Value = StoredValue
            .Cells(.Cells.Count, 1) = ItemsArr(1)
            .Cells(.Cells.Count - 1) = ItemsArr(2)
            .Cells(.Cells.Count - 2) = ItemsArr(3)
        End If
    End With
End Sub


Sub Pop()
    On Error Resume Next
    Set DinamicRange = Range("C2:C22")
    With DinamicRange
        Set PopulatedCells = .SpecialCells(xlCellTypeConstants)
        If PopulatedCells Is Nothing Then
            MsgBox "No more Items to pull !", vbCritical
        Else
            StoredValue = PopulatedCells.Value
            Set PopulatedCells = PopulatedCells.Offset(1).Resize(PopulatedCells.Offset(1).Rows.Count)
            PopulatedCells.Value = StoredValue
            PopulatedCells.Cells(1).Offset(-1).ClearContents
            PopulatedCells.Cells(PopulatedCells.Rows.Count).ClearContents
        End If
    End With
End Sub

Regards.

 

[nomad815]

..........................................................................................................