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:
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:
Rich (BB code):
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
Last edited by a moderator: