4). Using the worksheet Data attached file, write a sub that will do the folowin

Question

4). Using the worksheet Data attached file, write a sub that will do the folowing: an anchor cell b). locate the cell where the word Bins is located c). declare a dynamic array (name it Bins) as a single and a dynamic array (name it cnt) as an integer d). count the number of bins using range properties (ie., count, etc) e). re-dimension the array Bins to have the number of bins specified in the worksheet and determine in d) f). re-dimension the array Cnt to have the number of bins plus one g). write a sub that will count the number of times the "amount purchased is below the first value in the Bins array and place in the first element of the array Cnt, the number of times the amount purchased" is greater than the first value and less than or equal to the second value in the Bins array and place it in the second element of array Cnt, and so on. The last element of the array Cnt will include the number of times the "amount purchased exceeds the last value in the Bins array h). write next to the column for the Bins, the numbers collected in the Cnt array, Note that the Cnt array has one more element than the Bins array. Your code should run for a different number of Bins and a different data set with A3 as anchor cell. For the example given in the worksheet, the column for Counts will look as follows: Counts Bins 75 100 294 500 63 700 1000 53

Explanation / Answer

1)to add anchor
Sub A3()

    Dim r As Range

    Set r = ActiveSheet.Range("A3")

    r.Parent.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:= "Sheet1!A0", TextToDisplay:="this is Anchor cell"
End sub

2)to find the word Bins
With Worksheets(1).Range("a1:a100")
    Set c = .Find(Bins, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
   do      
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

3)to declare dynamic array,place this under any command button

Dim Bins(), sizeOfBins
Dim Cnt() As Integer, sizeOfCnt As Integer, i As Integer
sizeOfBins = WorksheetFunction.CountA(Worksheets(1).Columns(1))
sizeOfCnt = WorksheetFunction.CountA(Worksheets(1).Columns(2))
ReDim numbers(sizeOfCnt)
For i = 1 To sizeOfCnt
    numbers(i) = Cells(i, 1).Value
Next i
MsgBox numbers(sizeOfCnt)

This will initialize value to 1, now enter the requierd value for Cnt and check

4)to find the number of filled rows in bins column

Dim BinsCount as Long
BinsCount = Application.WorksheetFunction.CountA(Columns(1))
msgbox BinsCount

5) to redimension the arrays

ReDim Preserve numbers(sizeOfBins)
MsgBox numbers(1)

ReDim Preserve numbers(sizeOfBins+1)
MsgBox numbers(2)

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.