I wanted to know of a way to call another sub to produce a message box. Example:
I have a script that pulls information of products and prices combines then produces output in two other columns. I then have a message box that tell how many different products there are in these two columns.
I want to have the message box in another sub (to call upon) with the information. It works but want product the number correctly. Code below:
Option Explicit
Option Base 1
Sub ProductSales()
' Call clear sub.
Call Clear
End Sub
Public Function Sales()
' These are inputs: the number of transactions, the product code for each
' sale, and the dollar amount of each sale.
Dim nSales As Integer
Dim productCodesData() As Integer
Dim dollarsData() As Single
' The following are outputs: the product codes found, the number of transactions
' for each product code found, and total dollar amount for each of them.
Dim productCodesFound() As Integer
Dim transactionsCount() As Integer
Dim dollarsTotal() As Single
' Variables used in finding unique product codes.
Dim isNewProduct As Boolean
Dim nFound As Integer
' Counters.
Dim i As Integer
Dim j As Integer
' Find number of sales in the data set, redimension the productCodesData and
' dollarsData arrays, and fill them with the data in columns A and C.
With Range("A2")
nSales = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
ReDim productCodesData(nSales)
ReDim dollarsData(nSales)
For i = 1 To nSales
productCodesData(i) = .Offset(i, 0).Value
dollarsData(i) = .Offset(i, 2).Value
Next
End With
' Initialize the number of product codes found to 0.
nFound = 0
' Loop through all transactions.
For i = 1 To nSales
' Set the Boolean isNewProduct to True, and change it to False only
' if the current product code is one already found.
isNewProduct = True
If nFound > 0 Then
' Loop through all product codes already found and compare them
' to the current product code.
For j = 1 To nFound
If productCodesData(i) = productCodesFound(j) Then
' The current product code is not a new one, so update
' its transactionsCount and dollarsTotal values appropriately, and
' exit this inner loop.
isNewProduct = False
transactionsCount(j) = transactionsCount(j) + 1
dollarsTotal(j) = dollarsTotal(j) + dollarsData(i)
Exit For
End If
Next
End If
If isNewProduct Then
' The current product code is a new one, so update the list of
' codes found so far, and initialize the transactionsCount and dollarsTotal
' values for this new product.
nFound = nFound + 1
ReDim Preserve productCodesFound(nFound)
ReDim Preserve transactionsCount(nFound)
ReDim Preserve dollarsTotal(nFound)
productCodesFound(nFound) = productCodesData(i)
transactionsCount(nFound) = 1
dollarsTotal(nFound) = dollarsData(i)
End If
Next
' Place the results in columns E to G.
For j = 1 To nFound
With Range("E2")
.Offset(j, 0).Value = productCodesFound(j)
.Offset(j, 1).Value = transactionsCount(j)
.Offset(j, 2).Value = dollarsTotal(j)
End With
Next
' Sort on column G in descending order, and display an appropriate message.
Range("E3").Sort Key1:=Range("G3"), Order1:=xlDescending, Header:=xlYes
MsgBox "There are " & nFound & " different products that have been sold."
End Function
Sub Clear()
' Clear any old results in columns E to G.
With Range("E2")
Range(.Offset(1, 0), .Offset(0, 2).End(xlDown)).ClearContents
End With
Call Sales
End Sub
Bookmarks