How to Replace Copied Values in an Excel Table with New Calculated Values and a Button

In this article, we will learn how to replace copied values in an Excel table with new calculated values and a button. This is useful when you want to apply a formula to a column of data that has been copied from another source, and you want to update the values automatically with a click of a button.

To replace copied values in an Excel table with new calculated values and a button, we need to use the following steps:

1. Create a table with the copied data and the desired formula in a separate column.
2. Insert a button in the worksheet and assign a macro to it.
3. Write a VBA code that will loop through the table rows and replace the copied values with the formula results.
4. Run the macro by clicking the button.

Procedures

Step 1: Create a table with the copied data and the desired formula in a separate column

Let’s assume we have a table with the following data that has been copied from another source:

We want to prorate a delivery cost of $600 to the unit price of each product, based on the proportion of the product amount to the total balance. The formula for the new unit price is:

New Unit Price=(Product Amount/Total Balance)×Delivery Cost​ / Product Quantity + Unit Price

We can enter this formula in a new column in the table, using cell references and named ranges. For example, in cell G2, we can enter:

=IF(ISNUMBER($Y$9),(($F2/$Y$10)*$Y$9)/$E2,0)+$F2

Where:

• $Y$9 is the named range nCostLogT that contains the delivery cost of $600.
• $Y$10 is the named range nTotalBalance that contains the sum of the product amount column, which is $8000.
• $E2 is the product quantity for the first row, which is 100.
• $F2 is the unit price for the first row, which is $20.

We can then copy this formula down to the rest of the column. The table will look like this:

Step 2: Insert a button in the worksheet and assign a macro to it

To insert a button in the worksheet, we can use the following steps:

1. Go to the Developer tab and click Insert in the Controls group.
2. Select a button from the Form Controls section and draw it on the worksheet.
3. In the Assign Macro dialog box, enter a name for the macro, such as Prorate_UnitPrice, and click New.

This will open the Visual Basic Editor with a blank macro.

Step 3: Write a VBA code that will loop through the table rows and replace the copied values with the formula results

To write a VBA code that will loop through the table rows and replace the copied values with the formula results, we can use the following steps:

1. Declare the variables and objects that we will use in the macro, such as the table, the worksheet, and the row index.
2. Set the table object to the table that we created in step 1, using the ListObjects collection and the table name, which is tCotizacion.
3. Set the worksheet object to the worksheet that contains the table, using the Worksheet property of the table object.
4. Loop through the table rows, starting from the second row, using a For Next loop and the ListRows collection of the table object.
5. For each row, replace the value in the unit price column with the value in the new unit price column, using the Range property of the row object and the Cells property of the range object.
6. End the loop and the macro.

The VBA code will look like this:

Sub Prorate_UnitPrice()
    'Declare variables and objects
    Dim ttCotizacion As ListObject
    Dim ws As Worksheet
    Dim i As Long
    
    'Set the table object to the table named tCotizacion
    Set ttCotizacion = ListObjects("tCotizacion")
    
    'Set the worksheet object to the worksheet that contains the table
    Set ws = ttCotizacion.Worksheet
    
    'Loop through the table rows, starting from the second row
    For i = 2 To ttCotizacion.ListRows.Count
        'Replace the value in the unit price column with the value in the new unit price column
        ws.Range(ttCotizacion.ListRows(i).Range.Cells(1, 2), ttCotizacion.ListRows(i).Range.Cells(1, 2)).Value = _
        ws.Range(ttCotizacion.ListRows(i).Range.Cells(1, 4), ttCotizacion.ListRows(i).Range.Cells(1, 4)).Value
    Next i
    
End Sub

Step 4: Run the macro by clicking the button

To run the macro by clicking the button, we can use the following steps:

1. Go back to the worksheet and click the button that we inserted in step 2.
2. The macro will run and replace the copied values in the unit price column with the new calculated values.
3. The table will look like this: