How to update a specific cell in a table from VBA?

[JenniferMurphy]

What is the VBA code I would need to use to update the Quan cell for Item C? I want to use Table addressing, not "D8". That is, I will have found item C and now I want to update its Quan value.

Suppose I have the name of the table stored in the VBA variable rnTable.

Tables.xlsx
	C	D
5	Item	Quan
6	A	10
7	B	25
8	C
9	D	12
10	E	5
VBA Access

 

[MARK858]

Maybe

rnTable.DataBodyRange.Cells(3, rnTable.ListColumns("Quan").Index).Value = "xxx"

or

rnTable.ListColumns("Quan").DataBodyRange(3, 1).Value = "zzzz"

 

[JenniferMurphy]

Maybe

rnTable.DataBodyRange.Cells(3, rnTable.ListColumns("Quan").Index).Value = "xxx"

or

rnTable.ListColumns("Quan").DataBodyRange(3, 1).Value = "zzzz"

Do I first have to search the table for the row that "C" is on? How do I do that?

 

[MARK858]

Maybe something like...

Sub FindC()
    Dim ColRng As Range, FindRow As Range
    Dim rnTable As ListObject
    
    Set rnTable = ActiveSheet.ListObjects(1)
    
    Set ColRng = rnTable.ListColumns("Item").Range
    Set FindRow = ColRng.Find(What:="C", After:=ColRng(1))

    If Not FindRow Is Nothing Then MsgBox FindRow.Row - rnTable.HeaderRowRange.Row

End Sub

 

[JenniferMurphy]

Maybe something like...

Sub FindC()
    Dim ColRng As Range, FindRow As Range
    Dim rnTable As ListObject
   
    Set rnTable = ActiveSheet.ListObjects(1)
   
    Set ColRng = rnTable.ListColumns("Item").Range
    Set FindRow = ColRng.Find(What:="C", After:=ColRng(1))

    If Not FindRow Is Nothing Then MsgBox FindRow.Row - rnTable.HeaderRowRange.Row

End Sub

If I load the table into a VBA array, then any changes I make are only to the VBA copy, right?

ListObjects, on the other hand, are "links" to the actual data in the sheet. So any changes I make will be updated in the sheet, right?

 

[JenniferMurphy]

I got this code to work to allow the user to modify the Quan value for any item in the table. It seems a little clunky. Is there anything I can do to simplify it or is there a better way?

Sub ListObjectTest2()
Const rnTable As String = "TblTest"
Dim loTable As ListObject
Dim ColRng As Range
Dim FindRow As Range
Dim Item As String
Dim RowNum As Long
Dim QuanCol As Long
Dim Value As Variant

' Define the ListObject variable for the table
On Error GoTo BadTableName
  Set loTable = Range(rnTable).ListObject
On Error GoTo 0
  GoTo TableNameOK
BadTableName:
  MsgBox "Invalid table name (" & rnTable & ")", vbOKCancel
  Exit Sub
TableNameOK:

QuanCol = loTable.ListColumns("Quan").Index

Item = InputBox("Enter the item or 'qq' to quit", MyName)
If UCase(Item) = "QQ" Then Exit Sub
Set ColRng = loTable.ListColumns("Item").Range
Set FindRow = ColRng.Find(What:=Item, After:=ColRng(1))
If FindRow Is Nothing Then
  MsgBox "Item '" & Item & "' not found"
Else
  RowNum = FindRow.Row - loTable.HeaderRowRange.Row
  Value = InputBox("Enter new value for Item '" & Item & "'")
  loTable.DataBodyRange(RowNum, QuanCol) = Value
End If

MsgBox "Done", vbOKCancel

End Sub

Thanks