rorobear
06-23-2023, 05:59 AM
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
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