Extracting data from a table and entering it into specific cells on a sheet

[Tony_Y]

Good evening,

I have been using a code for some time now which extracted data from a table and entered the information into specific cells on another worksheet within the same workbook. Now for some reason it is giving me a runtime error 91 Object variable or with block variable not set. When I go to debug it is highlighting the row "f = fCell.Row"
Any help or a point in the right direction will be greatly appreciated.

Tony

My code is:

Sub DataToAmend()

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

Application.ScreenUpdating = False


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

x = fCell.Row

With ws

Sheets("DATA AMEND").Range("Amensite_add1").Value = Sheets("DATA TABLE").Cells(x, 5).Value
Sheets("DATA AMEND").Range("Amendsite_add2").Value = Sheets("DATA TABLE").Cells(x, 6).Value
Sheets("DATA AMEND").Range("Amendsite_post").Value = Sheets("DATA TABLE").Cells(x, 7).Value
Sheets("DATA AMEND").Range("Amendsite_cont").Value = Sheets("DATA TABLE").Cells(x, 8).Value
Sheets("DATA AMEND").Range("Amendclient_name").Value = Sheets("DATA TABLE").Cells(x, 9).Value
Sheets("DATA AMEND").Range("Amendclient_add1").Value = Sheets("DATA TABLE").Cells(x, 10).Value
Sheets("DATA AMEND").Range("Amendclient_add2").Value = Sheets("DATA TABLE").Cells(x, 11).Value
Sheets("DATA AMEND").Range("Amendclient_post").Value = Sheets("DATA TABLE").Cells(x, 12).Value
Sheets("DATA AMEND").Range("Amendclient_cont").Value = Sheets("DATA TABLE").Cells(x, 13).Value
Sheets("DATA AMEND").Range("Amendmeter_des").Value = Sheets("DATA TABLE").Cells(x, 14).Value
Sheets("DATA AMEND").Range("Amendpipe_mat").Value = Sheets("DATA TABLE").Cells(x, 18).Value
Sheets("DATA AMEND").Range("Amendmip").Value = Sheets("DATA TABLE").Cells(x, 19).Value
Sheets("DATA AMEND").Range("Amendmop").Value = Sheets("DATA TABLE").Cells(x, 20).Value
Sheets("DATA AMEND").Range("Amendop").Value = Sheets("DATA TABLE").Cells(x, 21).Value
Sheets("DATA AMEND").Range("Amendinc_10").Value = Sheets("DATA TABLE").Cells(x, 23).Value
Sheets("DATA AMEND").Range("Amendtest_gas").Value = Sheets("DATA TABLE").Cells(x, 24).Value
Sheets("DATA AMEND").Range("Amendop_gas").Value = Sheets("DATA TABLE").Cells(x, 25).Value
Sheets("DATA AMEND").Range("Amendguage").Value = Sheets("DATA TABLE").Cells(x, 26).Value
Sheets("DATA AMEND").Range("Amendinst_type").Value = Sheets("DATA TABLE").Cells(x, 27).Value
Sheets("DATA AMEND").Range("Amendarea_type").Value = Sheets("DATA TABLE").Cells(x, 28).Value
Sheets("DATA AMEND").Range("Amendsmall").Value = Sheets("DATA TABLE").Cells(x, 29).Value
Sheets("DATA AMEND").Range("Amendpurge").Value = Sheets("DATA TABLE").Cells(x, 30).Value
Sheets("DATA AMEND").Range("AmendEng_note").Value = Sheets("DATA TABLE").Cells(x, 32).Value

Sheets("DATA AMEND").Visible = xlSheetVisible
Sheets("INPUT GUIDE").Visible = xlSheetHidden
Sheet21.Activate
End With

End Sub

 

[Fluff]

That means the search value cannot be found.

 

[Tony_Y]

Ah I see. I don't know what had changed but it's sorted. Many thanks for the prompt reply Fluff. Greatly appreciated once more.

Tony

 

[Fluff]

You're welcome & thanks for the feedback.