Update cells in a table using the text in column A as the reference to find the correct row

[Tony_Y]

Hi everyone,

I have a worksheet called DATA TABLE which has some 30+ columns. Located in column A is the specific site name. I have another worksheet called DATA AMEND which I am able to populate specific cells with information from DATA TABLE. I wish to then alter any of the cells in DATA AMEND and use a shape as a command button to transfer the data back into the correct row and column of the DATA TABLE.

To be honest I've been at this weeks and pulled out that much hair and don't require a post lockdown haircut!

Any pointers in the right direction will be appreciated.

regards

Tony

 

[Fluff]

Ok, that's fine.

 

[Tony_Y]

Good morning,

I have amended it (incorrectly I suspect) as I am still getting the 424 error. Sorry to be a pain.

TIA

Tony

Code

Private Sub AmendDataSht()

Dim Ws As Worksheet
Dim x As Long
Dim fCell As Range
Set Ws = ThisWorkbook.Sheets("DATA TABLE")

Application.ScreenUpdating = False

Private Sub AmendDataSht()


Dim Ws As Worksheet
Dim x As Long
Dim fCell As Range
Set Ws = ThisWorkbook.Sheets("DATA TABLE")

Application.ScreenUpdating = False

' Amendsite_name is the name given to cell D3 on the worksheet DATA AMEND. This is the sheet I am using to
' update the cells on the matching row located on the worksheet DATA TABLE[/CODE][/CODE]

Set fCell = Sheets("DATA TABLE").Range("Amendsite_name").Find(Amendsite_name.Value, , xlValues, xlWhole)
x = fCell.Row

With Ws

' Amendsite_add1 and Amensite_add2 is the name given to cells D7 and D9 on the worksheet DATA AMEND. This is the
' sheet I am using to update the cells on the matching row located on the worksheet DATA TABLE. In this case the
' cells are located in column 5 and 6

Sheets("DATA TABLE").Cells(x, 5).Value = Amendsite_add1.Value
Sheets("DATA TABLE").Cells(x, 6).Value = Amendsite_add2.Value


End With

End Sub

 

[Fluff]

It needs to be like

 Set fCell = Sheets("DATA TABLE").Range("A:A").Find(Range("Amendsite_name").Value, , xlValues, xlWhole)

and

 Sheets("DATA TABLE").Cells(x, 5).Value = Range("Amendsite_add1").Value

 

[Tony_Y]

Hi Fluff

Thank you for this. I am now getting a runtime error 1004 - Method Range of object Global failed.

I have highlighted the debug in red text.

TIA

Tony


Sub AmendDataSheet()

Dim Ws As Worksheet
Dim x As Long
Dim fCell As Range
Set Ws = ThisWorkbook.Sheets("DATA TABLE")

Application.ScreenUpdating = False

' Amendsite_name is the name given to cell D3 on the worksheet DATA AMEND. This is the sheet I am using to
' update the cells on the matching row located on the worksheet DATA TABLE

Set fCell = Sheets("DATA TABLE").Range("A:A").Find(Range("Amendsite_name").Value, , xlValues, xlWhole)

x = fCell.Row

With Ws

' Amendsite_add1 and Amensite_add2 is the name given to cells D7 and D9 on the worksheet DATA AMEND. This is the
' sheet I am using to update the cells on the matching row located on the worksheet DATA TABLE. In this case the
' cells are located in column 5 and 6

Sheets("DATA TABLE").Cells(x, 5).Value = Range("Amendsite_add1").Value
Sheets("DATA TABLE").Cells(x, 6).Value = Range("Amendsite_add2").Value


End With



End Sub

 

[Fluff]

Are you sure you have a named range called Amendsite_add1?
If you do is it workbook or sheet scoped?

 

[Tony_Y]

Hi Fluff,

You are a legend! I had missed the d from Amendsite when I named the cell!!!

Thank you so much for your help. It is greatly appreciated.

Many thanks

Tony

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Tony_Y]

Hi,

I am now progressing with my workbook and I am trying to populate named cells in the worksheet named DATA AMEND with cells from the sheet named DATA TABLE, using the cell named Amendsite_Name to locate the row in DATA TABLE. I have he code below but am getting Runtime error 1004 Application defined or object defined error. I have tried numerous fixes - none work.

Any assistance will be greatly appreciated.

TIA

Tony


VBA CODE

Sub ExtractAmendDataSheet()

Dim Ws As Worksheet
Dim x As Long
Dim fCell As Range
Set Ws = ThisWorkbook.Sheets("DATA TABLE")

Application.ScreenUpdating = False

' Amendsite_name is the name given to cell D3 on the worksheet DATA AMEND. This is the sheet I am using to
' locate the cells on the worksheet DATA TABLE

Set fCell = Sheets("DATA TABLE").Range("A:A").Find(Range("Amendsite_name").Value, , xlValues, xlWhole)

x = fCell.Row

With Ws

' Amend_Newsite_Name is the name given to a cells on the worksheet DATA AMEND. This is the
' sheet I am using to update the DATA TABLE. However, I first need to gather the existing information from
' DATA TABLE into DATA AMEND allowing me to alter it if required and send it back to DATA TABLE.
' I have the macro to send it back working ok.

Sheets("DATA AMEND").Range("Amend_Newsite_Name").Value = Sheets("DATA TABLE").Range(x, 1).Value

End With


End Sub

 

[Fluff]

It needs to be

 Sheets("DATA TABLE").Cells(x, 1).Value

 

[Tony_Y]

Hi Fluff

Yet again you have sorted it. Very many thanks.

Regards

Tony