VBA: ClearNotes in another Table

[zero269]

Hello,

I'm working on a VBA macro that will update a cells value and/or cell note in TableName "t_Books" based on a selection from another table.
I was able to get the cell values to work, but I'm unable to figure out how to work with the cells notes.

Using the below Sample Data - all in one sheet for testing.
If I select the green cell in the Student Table and run the macro, the Book Status in the Books Table will be updated based on the following criteria:
Cell value will change to library and the cell note will be cleared. The cell note is not showing in the MiniSheet, but the cellnote = 'library'.

VBA Testing.xlsm
	A	B	C	D	E	F	G	H	I
1	Books Table					Student Table
2	Quiz	Title	Author	Book Status		Quiz	Title	Author	Book Status
3	176888	A Funny Thing Happened on the Way to School...	Cali, Davide	on hold		176888	A Funny Thing Happened on the Way to School...	Cali, Davide	on hold
4	162306	A Tale of Two Sisters	Lagonegro, Melissa	on hold		162306	A Tale of Two Sisters	Lagonegro, Melissa	on hold
5	503552	Bad Kitty: Searching for Santa	Bruel, Nick	on hold		503552	Bad Kitty: Searching for Santa	Bruel, Nick	on hold
6	27484	Bathtime for Biscuit	Capucilli, Alyssa Satin	on hold		27484	Bathtime for Biscuit	Capucilli, Alyssa Satin	on hold
7	503651	Beneath the Bed and Other Scary Stories	Brallier, Max	available		192899	Dog Man and Cat Kid	Pilkey, Dav	library
8	187861	Better Call Batman!	Bright, J.E.	available		511040	Dog Man: Mothering Heights	Pilkey, Dav	library
9	192899	Dog Man and Cat Kid	Pilkey, Dav	library		168808	Fly Guy's Amazing Tricks	Arnold, Tedd	available
10	511040	Dog Man: Mothering Heights	Pilkey, Dav	library		9018	Foot Book	Seuss, Dr.	library
11	168808	Fly Guy's Amazing Tricks	Arnold, Tedd	available		124813	Mind Your Manners, Biscuit!	Capucilli, Alyssa Satin	available
12	9018	Foot Book	Seuss, Dr.	library		171841	Poky Little Puppy	Depken, Kristen	library
13	124813	Mind Your Manners, Biscuit!	Capucilli, Alyssa Satin	available
14	171841	Poky Little Puppy	Depken, Kristen	library
MatchUpdate

Cell Formulas
Range	Formula
I3:I12	I3	=XLOOKUP([@Quiz],t_Books_MatchUpdate[Quiz],t_Books_MatchUpdate[Book Status],"")

Here's what I've got so far:

Sub TEST_Update_Book_Status()

  'Select visible cells only if selection is > 1
  If Selection.Cells.Count > 1 Then
    Selection.SpecialCells(xlCellTypeVisible).Select
  Else
  End If
  
  'Declarations
  Dim cell As Range

  'LOOP Selection
  For Each cell In Selection
  
    'Store Selected Values in 'Student' table to Match in 'Books' table
    Dim BookStatus As String: BookStatus = cell.Value
    Dim Quiz As Long: Quiz = cell.Offset(0, -3).Value
    
    'Get Matching Row & Column Index in 'Books' table
    Dim lRow As Long, lCol As Long
    lRow = Application.WorksheetFunction.Match(Quiz, Range("t_Books_MatchUpdate[Quiz]"), 0)
    lCol = wsMatchUpdate.ListObjects("t_Books_MatchUpdate").ListColumns("Book Status").Index
  
    'Declare Books table
    Dim myTbl As ListObject
    Set myTbl = wsMatchUpdate.ListObjects("t_Books_MatchUpdate")
    
    'Update 'Book Status' and Notes in 'Books' table
    If myTbl.DataBodyRange.Cells(lRow, lCol) = "library" Then
      myTbl.DataBodyRange.Cells(lRow, lCol) = "on hold"
      
    ElseIf myTbl.DataBodyRange.Cells(lRow, lCol) = "on hold" Then
      myTbl.DataBodyRange.Cells(lRow, lCol) = "available"
      
    ElseIf myTbl.DataBodyRange.Cells(lRow, lCol) = "available" Then
      myTbl.DataBodyRange.Cells(lRow, lCol) = "library"

    ElseIf myTbl.DataBodyRange.Cells(lRow, lCol).Value = "on order" And myTbl.DataBodyRange.Cells(lRow, lCol).NoteText = "library" Then
      myTbl.DataBodyRange.Cells(lRow, lCol).Value = "library"
      myTbl.DataBodyRange.Cells(lRow, lCol).ClearNotes

    Else
    End If
  
  Next cell
  
  wsMatchUpdate.Calculate

End Sub

Below is what I'm currently using if I'm on the "Books" table. However, I'm trying to get the same functionality from any of the Student tables instead of having to manually search for the book by Quiz number and then updating the Book Status.

Sub Books_Update_Book_Status()

  'Select visible cells only if selection is > 1
  If Selection.Cells.Count > 1 Then
    Selection.SpecialCells(xlCellTypeVisible).Select
  Else
  End If
  
  'Declarations
  Dim cell As Range

  'LOOP Selection
  For Each cell In Selection
  
    'Update 'Book Status'
    If cell.Value = "library" Then
      cell.Value = "on hold"
      
    ElseIf cell.Value = "on hold" Then
      cell.Value = "available"
      
    ElseIf cell.Value = "available" Then
      cell.Value = "library"

    ElseIf cell.Value = "on order" And LCase(cell.NoteText) = "library" Then
      cell.Value = "library"
      cell.ClearNotes

    Else
    End If
  
  Next cell
  
  wsBooks.Calculate

End Sub

Any help would be greatly appreciated…

 

[zero269]

After more testing and research, I was able to find a solution to my problem. It turns out that I needed to use Range instead of ListObject for the myTbl variable.

Using ListObject:
myTbl.Value returned an error
myTbl returned the cells value

Eventually, I found others using Range with their code and gave it a try… and it worked the same as my original code (2nd one in my OP).

Failed Option using ListObject:

    'Declare Books table
    Dim myTbl As ListObject
    Set myTbl = wsMatchUpdate.ListObjects("t_Books_MatchUpdate")

Successful Option using Range:

'Set Working Range
Dim myRange As Range
Set myRange = wsMatchUpdate.ListObjects("t_Books_MatchUpdate").DataBodyRange(lRow, lCol)

My Final Test Code:

Sub Update_Book_Status()

  Dim cell As Range, myRange As Range
  Dim Quiz As Long, lRow As Long, lCol As Long
 
  For Each cell In Selection
 
    'Store Lookup Value
    Quiz = cell.Offset(0, -3).Value
  
    'Get Matching Row & Column in 'Books' table
    lRow = Application.WorksheetFunction.Match(Quiz, Range("t_Books_MatchUpdate[Quiz]"), 0)
    lCol = wsMatchUpdate.ListObjects("t_Books_MatchUpdate").ListColumns("Book Status").Index
  
    'Set Working Range
    Set myRange = wsMatchUpdate.ListObjects("t_Books_MatchUpdate").DataBodyRange(lRow, lCol)
  
    'Update Book Status
    If myRange.Value = "available" And LCase(myRange.NoteText) = "library" Then
      myRange.Value = "library"
      myRange.ClearNotes
    End If
 
  Next cell

End Sub