Updating a specific cell value in an autofiltered table

[CainyUK]

Hi all,

I'm struggling with a specific piece of my VBA script

to provide some context - i have to keep track of our own internal equipment , each has its own equipment ID tag ( EID ) , when checking the equipment as part of routine inspection, we have to then update a cell in a table that has a date in for when it was last checked

so i use this code to filter the table to only show the specific EID - Cells (1,1) in the sheet houses the EID number that is being searched

ActiveSheet.ListObjects("EquipmentReg").Range.AutoFilter Field:=5, Criteria1 _
        :=Cells(1, 1).Value

ive found on a forum some other code that ive adjusted which isnt quite working for me, but here is my adjusted version

Sub SubChangeAutofilteredValues()

    'Declarations.
    Dim RngRange01 As Range
    Dim StrOldValue As String
    Dim StrNewValue As String

    'Setting variables.
    StrOldValue = "<=" & Date
    StrNewValue = Date

    'Autofiltering.
    ActiveSheet.ListObjects("EquipmentReg").Range.AutoFilter Field:=11, Criteria1:=StrOldValue

    'Verifying if any match has been found.
    If Cells(ActiveSheet.ListObjects("EquipmentReg").Range.Rows.Count + 1, 1).End(xlUp).Row = 1 Then
        MsgBox "No records found.", , "No records found"
        Exit Sub
    End If

    'Setting the variable.
    With ActiveSheet.AutoFilter.Range
        Set RngRange01 = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
    End With

    'Changing the values.
    RngRange01.Value = StrNewValue

End Sub

What I'm wanting to do , is once ive already got my EID found and the table filtered , is to update the cell in column / Field 11 to change the date from its previous date ( always before today) and update it with todays date.

The 2nd script, is updating EVERY cell within the row with the new date value - i think it has something to do with the Set RngRange01 but cant figure this out

column/Field 5 - houses my EID that i will filter before hand - the item is then checked , and if passes inspection i want to use the a 2nd script to update the date cell relative to that item
column/Field 11 - houses the old date that needs updating

can someone please help me out ? whether its a new script or adjusting the 2nd script - greatly appreciated people !

 

[skillilea]

If everything is working but the date,
Try adjusting this line

Set RngRange01 = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).cells(1)

 

[CainyUK]

If everything is working but the date,
Try adjusting this line

Set RngRange01 = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).cells(1)

perfect , i adjust the final Cells(1) to cells (11) and its worked a treat ! cheers

 

[skillilea]

Happy to help