How to update the value in a range of cells using a Loop in Excel VBA

Question

I am currently working on an excel application where I have a macro, operated by a button click, which resets the numerical values within certain cells in a table.

Within this table there are 3 columns; "Quantity Fitted (n)", "Quantity Required (m)" and "Lock Configuration".

What I need to happen, is when the button is clicked, the numerical values for each line within the "Quantity Fitted (n)" column are reset to match the values displayed within the "Quantity Required (m)" column of the same line.

However, if the cell value within the "Lock Configuration" column of that row is set to "locked", I want the "Quantity Fitted (n)" value to remain unchanged after the button click.

I hope this makes sense! Here's my current attempt to code this:

Public Sub Reset_Quantity_Fitted()
'Macro to make quantity fitted equal to quantity required

    Dim rng As Range
    Dim cell As Range

    Set rng = Worksheets(ActiveSheet.Name).ListObjects("Table_" & ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange

For Each cell In rng.Cells

    If rng.Offset(, 5) = "Locked" Then
        cell = Worksheets(ActiveSheet.Name).ListObjects("Table_" & ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange
    Else
        cell = Worksheets(ActiveSheet.Name).ListObjects("Table_" & ActiveSheet.Name).ListColumns("Quantity Required (m)").DataBodyRange
    End If

Next cell

End Sub

This approach was recommended by another user on this site, however hen running this code I get the following error:

Run-time error '13': Type mismatch

Can anyone help me identify what is wrong with this code?

Answer 1

This code should do what you're asking:

Sub Test()

    Dim x As Long

    'Set reference to your table.  Have hard-coded the sheet name and table name in.
    Dim MyTable As ListObject
    Set MyTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table_Sheet1")

    'These will be the numerical index numbers of the columns in your table.
    'This assumes your "Locked" column is in the table and has the header "Locked".
    Dim Required As Long, Fitted As Long, Locked As Long
    Required = MyTable.ListColumns("Quantity Required (m)").Index
    Fitted = MyTable.ListColumns("Quantity Fitted (n)").Index
    Locked = MyTable.ListColumns("Locked").Index

    'Look at each row in the table.  Am using `x` rather than `Each Cell`
    'as the row number of the cell may differ from the row location in the table
    'e.g. If your table starts on row 2, then first row after the header is row 3 - row 3 as Cell, but row 1 in table.
    For x = 1 To MyTable.Range.Rows.Count
        'If Locked column doesn't say "Locked" then copy value from
        'Fitted to Required columns, otherwise do nothing.
        If MyTable.DataBodyRange.Cells(x, Locked) <> "Locked" Then
            MyTable.DataBodyRange.Cells(x, Fitted) = MyTable.DataBodyRange.Cells(x, Required)
        Else
            'Do Nothing.
            'The ELSE and comment aren't really needed - just here to show nothing happens.
        End If
    Next x

End Sub  

In response to your Set question - Set is used to assign a reference to an object.
Found this other question which may answer better. Consider the code below:

Sub Test2()

    Dim MyCellValue As String
    Dim MyCellReference As Range

    'Will only contain the string value held in A3 as
    'the default property of a cell is the value.
    MyCellValue = Sheet1.Range("A3")
    MyCellValue = 3 'This changes the value of MyCellValue.
    'MyCellValue.Font.Bold = True 'Doesn't compile as "Invalid Qualifier"

    'Holds a reference to A3.
    Set MyCellReference = Sheet1.Range("A3")
    MyCellReference = 1 'This changes the value held in cell A3.
    MyCellReference.Font.Bold = True

End Sub