VBA to take a field value from 1 worksheet and update that field value to another worksheet based on another cell value

[frothinmaddog]

Hi, its a Friday afternoon and my brain wont work at all. I have 2 worksheets. In one worksheet "Worksheet1" I have a defined table with 2500 rows of data with 2 fields, "ID", "Name" and "Amount". the values in the first "ID" column are unique and the "Amount" column is blank with nothing in it currently. I have a transposed version of that data in a second worksheet "Worksheet2" which represents a single record from Worksheet1 so that a User doesnt have to deal with trying to scroll through 2,500 rows of data. I've set up some data validation and a FILTER to bring in a single record from Worksheet1 into Worksheet2 based on the "ID" value that a user selects in Worksheet2.

E.g. A user selects "ID" value of "223" from a field drop down in Worksheet2 and they see the ID, Name and Amount fields in a transposed view (i.e. vertically arranged) corresponding to ID = 223 from Worksheet1. I want to find a way for a User to enter a value into the "Amount" field in Worksheet2 for a given "ID", and for that value to be updated to the "Amount" column for the corresponding "223" ID record in Worksheet1. I hope that makes sense.

Worksheet 1

ID	Name	Amount
912	Earnie
121	Jan
456	Barry
789	Reg

Worksheet 2 sample

ID	456
Name	Barry
Amount

In the above snapshot, I want a user to be able to enter an amount in Worksheet2 'Amount' field and for that value to make its way to the 'Amount' cell in Worksheet1 corresponding to ID "456"

Any help much appreciated.
Thanks

 

[frothinmaddog]

Sorry, "...data with 2 fields..." in the second sentence should say "...data with 3 fields..."

 

[frothinmaddog]

I've managed to get this far with the code noting in Worksheet1 my range is a defined excel table and the column/field I am updating the Amount value from Worksheet2 to is called [Amount]. Now I am getting a Runtime error 13: Type mismatch message when i try to run it. Ive checked both Amount fields in Worksheet are formatted in Excel as 'Number' types and there's no formulas in the cell. Can't figure out why I still get an error message. Any help appreciated. Thanks.

Sub UpdateAmount()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+r
'

  ' Define worksheets and ranges
  Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Worksheets("Worksheet1")
  Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Worksheets("Worksheet2")
  Dim idCell As Range: Set idCell = ws2.Range("ID")  ' Adjust cell reference as needed
  Dim amountCell As Range: Set amountCell = ws2.Range("Amount")  ' Adjust cell reference as needed

  ' Get the selected ID and new amount
  Dim selectedID As String: selectedID = idCell.Value
  Dim newAmount As Variant: newAmount = amountCell.Value

  ' Find the corresponding row in Worksheet1
  Dim rowNumber As Long
  With ws1
    rowNumber = .Rows.Find(selectedID, LookIn:=xlValues, SearchOrder:=xlByRows).Row
  End With
  
  Debug.Print "Value of rowNumber:", rowNumber

  ' Check if ID is found
  If rowNumber > 0 Then
    ' Update the Amount cell in Worksheet1
    Debug.Print "Value of newAmount:", newAmount
    
    If IsNumeric(newAmount) Then
    ws1.Cells(rowNumber, [Amount]).Value = newAmount
    Else
    ' Handle case where newAmount is not a number
    MsgBox "Invalid amount entered!", vbExclamation
End If
  Else
    ' Handle case where ID is not found (optional)
    MsgBox "ID not found in Worksheet1!", vbExclamation
  End If
End Sub

 

[frothinmaddog]

Worked it out. Closing the question.

 

[Peter_SSs]

Good to hear you got a solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.
Note that in the MrExcel forums, the mark to the right of a post is a "Solution" mark, not a "Thread closed" mark.