0

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?

Improve this question
5
  • 1
    Which line gives the error? Shouldn't If rng.Offset(, 5) = "Locked" Then be If Cell.Offset(, 5) = "Locked" Then Commented Mar 17, 2020 at 9:48
  • 1
    Worksheets(ActiveSheet.Name) can be shortened to just ActiveSheet - is the correct sheet active when you run the code? Are your tables named correctly? Commented Mar 17, 2020 at 9:52
  • 1
    cell is a single cell, Worksheets(ActiveSheet.Name).ListObjects("Table_" & ActiveSheet.Name).ListColumns("Quantity Fitted (n)").DataBodyRange is a column of cells. As you're not using Set when updating the cell reference it's probably returning the value from the first cell in the range - which on the first iteration would be the value of cell if the fifth column is Locked. Commented Mar 17, 2020 at 9:59
  • Hi @DarrenBartrup-Cook thank you for getting back to me. I made the change of: updating If rng.Offset(,5) = "Locked" Then for If cell.Offset(,5) = "Locked" Then This has now solved the mismatch error! Thank you! I hadn't spotted that. Unfortunately, it now simply deletes all values and leaves the cells blank. It's a step forward though, thanks :) Commented Mar 17, 2020 at 10:04
  • @DarrenBartrup-Cook sorry for the basic question, but how would I use Set when updating the cell? Commented Mar 17, 2020 at 10:11

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi @DarrenBartrup-Cook, I've tried to apply your suggestions as best I can and I'm still getting a type 13 mismatch error, I believe at the line Set MyTable = Worksheets("System").ListObjects("Table_System") - I'm really not sure where to go with this lol as any solution seems to throw up the same error.
Not sure what would cause that error on that line - I'd expect a Subscript out of range if the sheet or table name was wrong.
ok thank you, I'll look into it further. Thank you for all your help so far!
I've re-checked the code and now almost have it working! It works perfectly, except it seems to mis-count the rows and attempt to run the if statement beyond the final row in the table, resulting in an error? Would you have any idea why that would be? Worst case I can add in an error handler to catch this before it stops the code from running, as the main aim is achieved by this point. Thank you again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.