Update Excel table from a one column range on a different sheet

[rorobear]

hello All,

Hope all is well. Can someone please help me modify this code. I don't need a message to display what is on the table. What i'd like to do instead is to be able to makes changes in range E5:E13 and have those changes be reflected on the table. For example, if i change the location, or purpose or anyghing in that range, and click the Save the button, want the change update in the table. Always, your help and support is appreciated. The workbook is attached. Lastly, i also, requested help on excel forum: https://www.excelforum.com/excel-programming-vba-macros/1407662-update-excel-table-from-a-one-column-range-on-a-different-sheet.html#post5841270

v/
RoRo


Sub FindAndDisplayRow()
Dim ws As Worksheet
Dim tbl As ListObject
Dim searchValue As Variant
Dim foundRow As Range
Dim displayText As String
Dim cell As Range

' Set the worksheet and table variables
Set ws = ThisWorkbook.Worksheets("PersonnelSHEET")
Set tbl = ws.ListObjects("PersonnelTABLE")

' Get the search value from cell E3 on the "TravelCALENDAR" sheet
searchValue = ThisWorkbook.Worksheets("TravelCALENDAR").Range("E3").Value

' Loop through the table rows
For Each Row In tbl.DataBodyRange.Rows
' Check if any cell in the row matches the search value
If Application.WorksheetFunction.CountIf(Row, searchValue) > 0 Then
' Concatenate the values of the matching row
For Each cell In Row.Cells
displayText = displayText & cell.Value & vbCrLf
Next cell
Exit For ' Exit the loop after finding the first match
End If
Next Row

' Display the values in a message box
MsgBox displayText, vbInformation, "Matched Row"
End Sub

[June7]

Could use Find method in VBA instead of looping through rows. Consider:


Dim r As Integer, x As Integer
Sheets("PersonnelSHEET").SelectSheets("PersonnelSHEET").Range("A1").Select
Cells.Find(What:=Sheets("TravelCALENDAR").Range("E3"), After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
r = ActiveCell.Row
For x = 2 To 10
Sheets("PersonnelSHEET").Cells(r, x) = Sheets("TravelCALENDAR").Cells(x + 3, 5)
Next


Ever consider MS Access Calendar database template? https://www.microsoftaccessexpert.com/Microsoft-Access-Calendar-Database.aspx ?

[rorobear]

June7,

thank you for the reply, not too familiar with Access i'll give it a look later, right now just need to finish this work project. i tried replacing the loop with your code and continue receive run time error 438.

[June7]

The code works for me and in your workbook. However, I tested it in a procedure triggered with ribbon Run button.

As far as I can tell, procedure you posted is not called from anywhere in posted workbook. How do you trigger it?

What is message for error 438?


Debug > Compile triggers error "Invalid Qualifier". Should fix line the debugger chokes on.

[rorobear]

i've just been running it from the code module. but even assigning to the "Save" button on the sheet, i get error 438, which "Object doesn't support this property or method". perhaps i didn't apply the you code correctly. is this how you have it?


Sub FindAndDisplayRow() Dim ws As Worksheet
Dim tbl As ListObject
Dim searchValue As Variant
Dim foundRow As Range
Dim displayText As String
Dim cell As Range

' Set the worksheet and table variables
Set ws = ThisWorkbook.Worksheets("PersonnelSHEET")
Set tbl = ws.ListObjects("PersonnelTABLE")

' Get the search value from cell E3 on the "TravelCALENDAR" sheet
searchValue = ThisWorkbook.Worksheets("TravelCALENDAR").Range("E3").Value

Dim r As Integer, x As Integer
Sheets("PersonnelSHEET").SelectSheets("PersonnelSHEET").Range("A1").Select
Cells.Find(What:=Sheets("TravelCALENDAR").Range("E3"), After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
r = ActiveCell.Row
For x = 2 To 10
Sheets("PersonnelSHEET").Cells(r, x) = Sheets("TravelCALENDAR").Cells(x + 3, 5)
Next

' Display the values in a message box
MsgBox displayText, vbInformation, "Matched Row"
End Sub

[June7]

Apologies. Did not notice that two lines got merged into one in the post (common issue in this forum - I should have double-checked). Correction with modification to use your variables (move Dim line to top of procedure):


ws.Select
ws.Range("A1").Select
Cells.Find(What:=searchValue, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
r = ActiveCell.Row
For x = 2 To 10
ws.Cells(r, x) = Sheets("TravelCALENDAR").Cells(x + 3, 5)
NextYou can remove MsgBox code and unused variable declarations.

[rorobear]

works beautifully now, thank you kindly!!! i had been struggling with that.