For Exce VBA codes please and please comment your email address so that I could

Question

For Exce VBA codes please and please comment your email address so that I could send you the problem excel data file. Thanks.

a). use cell A3 as 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 (i.e., 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.

4). Using the worksheet Data in the attached file, write asub that wildothe oloeing a) use cell A3 as an anchor cell b) locate the cell where the word Bins is located c) declare a dynamic (name it as a sngle and a dymanic amay inametcnt) as d). count the number of bins using range properties count, etc) re-dimension the array Bins to have the number of bins specified in te worksheet and in d f). re-dimension the amay Cnt to have the number of bins plus one g), write a sub that wil count the number of times the "amount purchased is below the first value in the Bins array and place in the finst element of the arrayCnt the number of times the amount purchased is greaterthan the first value and less than or equaltothe second value in the Bins amay and place itin the second element of amay Crt and so on. The last element of the array Ont will include the number of tmes the "amount purchased exceeds the last value in the Bins array. h), write next to the column for the Bins, the numbers colected in the Crt amay, Note that the Cnt array has one more element the Bins code number of Bins and a diferent data set wthA3 as For the example given in the worksheet, the column for Counts wil ook as folows Bins Counts 100 75 700 63 1000 53

Explanation / Answer

Answer: See the code below:

----------------------------------

Public Sub DoTask()
Dim A3
A3 = Range("A3")
Set BinsCell = ActiveSheet.Cells.Find("Bins")
Dim Bins() As Single
Dim Cnt() As Integer
Dim startCell
startCell = Cells(BinsCell.Row + 1, BinsCell.Column)
Dim BinsCount
Cnt = Application.WorksheetFunction.CountA(Columns(startCell.Column))
ReDim Bins(BinsCount)
ReDim Cnt(BinsCount + 1)
Dim amountPurchased
Dim amountCount
Dim Value, PrevValue
PrevValue = 0
'populate amountPurchased here
For i = 1 To BinsCount + 1
    amountCount = 0
    For j = 1 To BinsCount
        Value = Bins(j)
        If PrevValue = 0 And amountPurchased < Value Then
            amountCount = amountCount + 1
        ElseIf amountPuchased > PrevValue And amountPurchased <= Value Then
            amountCount = amountCount + 1
        End If
        PrevValue = Value
    Next j
    Cnt(i) = amountPurchased
Next i
Dim nextCell
nextCell = Cells(BinsCell.Row, BinsCell.Column + 1)
nextCell.Value = "Counts"
Range("Counts") = Cnt
End Sub

--------------------------

Note: This is a untested code for your reference. For final code, access to actual data and requirement is required.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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