Create Dynamic Range Decision Making with Excel VBA

Concept: Creating a Dynamic Range for Decision-Making in VBA

In decision-making scenarios, we might want to define a dynamic range where different conditions or criteria are evaluated to make decisions, like filtering data, selecting the appropriate subset of rows, or performing operations based on conditions. VBA can help automate this process efficiently by creating dynamic ranges that adapt as data changes.

We will develop a VBA code that:

1. Creates a dynamic range.
2. Evaluates a set of decision-making criteria.
3. Takes actions based on the evaluation.

Scenario:

Imagine you have a dataset in Excel containing sales transactions with the following columns:

• Product Name
• Quantity Sold
• Sales Amount
• Salesperson
• Date

We need to dynamically select ranges based on certain conditions (e.g., sales amount > $1000 or specific salesperson). Based on these conditions, the VBA code will make decisions like highlighting the rows or performing some calculations.

Step-by-step Process:

1. Set the Range Dynamically:
   • The range will adapt based on the number of rows in your dataset. This ensures that even as data is added or removed, the range always includes all data without the need for manual adjustment.
2. Conditionally Select Data:
   • Once the dynamic range is identified, you can apply conditions to filter specific data (e.g., sales over $1000).
3. Decision-Making:
   • The VBA code will decide what to do based on your conditions (highlighting, summing, etc.).

Example VBA Code for Dynamic Range Decision-Making:

Sub DynamicRangeDecisionMaking()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dynamicRange As Range
    Dim cell As Range
    Dim criteria As Double
    Dim sumSales As Double   
    ' Set reference to the worksheet
    Set ws = ThisWorkbook.Sheets("SalesData")   
    ' Find the last row of data in the sales data (assuming data starts in row 2)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Define dynamic range from row 2 to the last row in the 'Sales Amount' column (Column C)
    Set dynamicRange = ws.Range("A2:E" & lastRow)   
    ' Set the decision criteria - sales amount greater than 1000
    criteria = 1000   
    ' Initialize the total sales variable
    sumSales = 0   
    ' Loop through each row in the dynamic range
    For Each cell In dynamicRange.Columns(3).Cells ' Column C (Sales Amount)
        If cell.Value > criteria Then
            ' If sales amount is greater than 1000, highlight the row in yellow
            cell.EntireRow.Interior.Color = RGB(255, 255, 0)           
            ' Add sales amount to the sum
            sumSales = sumSales + cell.Value
        Else
            ' If sales amount is less than or equal to 1000, highlight in red
            cell.EntireRow.Interior.Color = RGB(255, 0, 0)
        End If
    Next cell  
    ' Display the total sales of the selected rows
    MsgBox "The total sales from transactions over $" & criteria & " is $" & sumSales
End Sub

Detailed Explanation:

1. Worksheet Setup:

Set ws = ThisWorkbook.Sheets(« SalesData »)

Here, we set the worksheet where our data is located (in this case, a sheet called « SalesData »).

2. Finding the Last Row:

lastRow = ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row

This line dynamically finds the last row of data in column A (assuming column A has no blank rows). It ensures the range is adjusted to include all rows in the dataset.

3. Defining the Dynamic Range:

Set dynamicRange = ws.Range(« A2:E » & lastRow)

The dynamic range starts from A2 to the last row in column E (which represents the full dataset). This ensures the range will always include the correct rows even if data is added or removed.

4. Condition (Sales Amount > $1000):

criteria = 1000

The criteria for the decision-making is set to $1000. The code will evaluate the sales amount and perform actions based on whether the sales value is greater than this criteria.

5. Looping Through the Range and Decision Making:

• For Each cell In dynamicRange.Columns(3).Cells

If cell.Value > criteria Then

‘ Action for sales > $1000

Else

‘ Action for sales <= $1000

End If

• Next cell

We loop through each cell in the third column (Sales Amount). If the sales amount is greater than the set criteria ($1000), we perform one action (highlighting the row in yellow). If it’s less than or equal to the criteria, we highlight the row in red.

6. Summing the Sales:

• sumSales = sumSales + cell.Value

We sum the sales values of all transactions where the sales amount exceeds the criteria.

7. Displaying Results:

• MsgBox « The total sales from transactions over $ » & criteria &  » is $ » & sumSales

After processing the data, the code displays a message box showing the total sales of transactions where the sales amount is greater than $1000.

Conclusion:

This VBA code helps automate decision-making processes by dynamically adjusting the range to your data and performing different actions based on conditions. By adjusting the condition (e.g., sales amount), you can easily adapt this code to different scenarios, making it highly flexible for your needs.