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

Tony_Y

New Member
Joined
May 29, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • 2020-07-08.png
    2020-07-08.png
    75.8 KB · Views: 21

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
It needs to be like
VBA Code:
 Set fCell = Sheets("DATA TABLE").Range("A:A").Find(Range("Amendsite_name").Value, , xlValues, xlWhole)
and
VBA Code:
 Sheets("DATA TABLE").Cells(x, 5).Value = Range("Amendsite_add1").Value
 
Upvote 0
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
 
Upvote 0
Are you sure you have a named range called Amendsite_add1?
If you do is it workbook or sheet scoped?
 
Upvote 0
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
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0
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
 
Upvote 0
It needs to be
VBA Code:
 Sheets("DATA TABLE").Cells(x, 1).Value
 
Upvote 0
Hi Fluff

Yet again you have sorted it. Very many thanks.

Regards

Tony
 
Upvote 0

Forum statistics

Threads
1,224,735
Messages
6,180,635
Members
452,991
Latest member
JM_000888

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top