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

 

[steve the fish]

Whats happening with what you have?

 

[braindiesel]

Using this sheet (from a previous posters question)

MFR

date beg

date end

price

MFR

date beg

date end

price

check(outuput column)

XXX4404444044

XXX-ltd

8/1/2020​

8/1/2020​

100​

XXX4404444044

XXX

8/1/2020​

8/1/2020​

100​

Match

YYY4407544075

YYY-ltd

9/1/2020​

9/1/2020​

200​

YYY4407544044

YYY

9/1/2020​

8/1/2020​

No Match

Match

GGG4410544105

GGG-ltd

10/1/2020​

10/1/2020​

300​

MMM4413644136

MMM-ltd

11/1/2020​

11/1/2020​

400​

This code (once amended) should run through your updates

Sub matcher()
    Do Until ActiveCell.Value = ""
        Range("H1").Select
        mySearch = ActiveCell
        myUpdate = ActiveCell.Offset(0, 1).Value
        myRow = WorksheetFunction.Match(mySearch, Range("A2:A5"), 0) + 1
        Range("B" & myRow).Value = myUpdate
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

 

[Tony_Y]

Hi

thanks for the prompt response.The code I have just goes to debug. I am back on it tomorrow and will update you.

regards

Tony

 

[steve the fish]

If you get an error you need to say which line and what the error says.

 

[Tony_Y]

Hi Steve

This is what happens. Sorry for late reply

 

[Fluff]

You need to use

Range("Amendsite_Name").value

Also, in future please post your code to the site, otherwise potential helpers have to type it out again.

 

[Tony_Y]

Hi Fluff,

I apologise for that and will do in future. Many thanks for your help.

regards

Tony

 

[Fluff]

Did that resolve your problem?

 

[Tony_Y]

Hi,

I am out on a job at the moment and will try it in the morning. I will report back as soon as I try it.
once again many thanks

Tony