Automate data augmentation processes in Excel using VBA.

Goal of the VBA Code

Let’s assume we have a dataset in Excel, and we want to automate the process of increasing or augmenting this data. For example, we want to:

1. Add new rows of data with randomly generated values.
2. Modify existing values.
3. Apply some transformations to certain columns (like increasing numeric values or modifying text).

Example VBA Code to Automate Data Augmentation

Here is an example of a VBA code that adds new rows with randomly generated data, modifies existing values, and applies transformations.

Steps to Insert and Run the VBA Code in Excel:

1. Open the VBA Editor: Press ALT + F11 to open the VBA editor.
2. Insert a New Module: In the VBA editor, go to Insert > Module.
3. Copy and Paste the Code below into the module.

Sub AugmentData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim newRow As Long
    Dim randomValue As Double
    Dim randomText As String   
    ' Set the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change sheet name as necessary   
    ' Find the last row of data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row   
    ' Add 10 new rows of data
    For i = 1 To 10 ' We are adding 10 rows here, you can adjust this number
        newRow = lastRow + i       
        ' Example of generating random data for columns A, B, and C
        ' Column A: Incrementing numerical value
        ws.Cells(newRow, 1).Value = ws.Cells(newRow - 1, 1).Value + Int(Rnd() * 10) + 1 ' Increment previous value with a random number       
        ' Column B: Random numerical value
        ws.Cells(newRow, 2).Value = Rnd() * 100 ' Random number between 0 and 100       
        ' Column C: Randomly generated text
        randomValue = Int(Rnd() * 4) ' Create a random number between 0 and 3
        Select Case randomValue
            Case 0
                randomText = "Product A"
            Case 1
                randomText = "Product B"
            Case 2
                randomText = "Product C"
            Case 3
                randomText = "Product D"
            Case Else
                randomText = "Unknown Product"
        End Select
        ws.Cells(newRow, 3).Value = randomText ' Add random text to column C       
        ' You can add more columns and transformations here if needed
    Next i
    MsgBox "Data augmentation complete!", vbInformation
End Sub

Explanation of the Code

Set the Worksheet:

Set ws = ThisWorkbook.Sheets("Sheet1")

This line sets the active worksheet where you want to add data. Be sure to adjust the sheet name (Sheet1) to match your actual sheet.

Find the Last Used Row:

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

This line finds the last used row in column A. It’s useful for determining where new rows will be added.

Loop to Add New Rows:

For i = 1 To 10
    newRow = lastRow + i

This loop adds 10 new rows. You can change the To 10 to add more or fewer rows.

Generate Random Values for Each Column:

• • Column A: A value in column A is incremented by a random number.

ws.Cells(newRow, 1).Value = ws.Cells(newRow - 1, 1).Value + Int(Rnd() * 10) + 1

1. • Column B: A random value between 0 and 100 is generated for column B.

ws.Cells(newRow, 2).Value = Rnd() * 100

1. • Column C: Random text is generated for column C using a Select Case statement.

randomValue = Int(Rnd() * 4)
Select Case randomValue
    Case 0
        randomText = "Product A"
    Case 1
        randomText = "Product B"
    Case 2
        randomText = "Product C"
    Case 3
        randomText = "Product D"
End Select

Confirmation Message:

MsgBox "Data augmentation complete!", vbInformation

This line displays a message box when the process of adding new rows is complete.

Customizing the Code

• You can modify the random data generators to suit your needs. For instance, if you have specific columns with formulas or need to apply complex transformations, you can modify those sections of the code.
• You can extend the process to apply more complex transformations to the existing data, such as doubling certain values, applying conditions, or concatenating strings.

Conclusion

This VBA code helps automate the process of augmenting data in Excel by adding new rows with randomly generated values. You can customize this code based on your specific needs, whether you’re working with numeric data, text, or other types of data transformations.