Search in multiple sheets

dado6612

Well-known Member
Joined
Dec 4, 2009
Messages
591
Hello, I have this code which works perfectly if active sheet has needed search value, otherwise it's an error.
How to adapt this so it searches thru more sheets instead. Thanks
The code is in plan report workbook, looking a value from weekly plan workbook and searches withing plan report workbook, then copies offset cells back to weekly plan workbook.

Code:
Sub search()Application.ScreenUpdating = False
Dim rng As Range
Dim order As String
Dim rn As Long
Dim name As String
Dim county As String
Dim part As String
Dim desc As String
Dim active As Range
Windows("Weekly Plan.xls").Activate
Set active = ActiveCell
order = ActiveCell.Text
Windows("Plan_Report.xlsm").Activate
Set rng = ActiveWorkbook.ActiveSheet.Columns("D:D").Find(what:=order, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    rn = rng.Row
    name = ActiveWorkbook.ActiveSheet.Cells(rn, 5).Value
    county = ActiveWorkbook.ActiveSheet.Cells(rn, 12).Value
    part = ActiveWorkbook.ActiveSheet.Cells(rn, 6).Value
    desc = ActiveWorkbook.ActiveSheet.Cells(rn, 11).Value
    Windows("MASTER SILO Weekly Plan 2018.xls").Activate
    active.Offset(-4, 0).Value = name
    active.Offset(-3, 0).Value = county
    active.Offset(-2, 0).Value = desc
    active.Offset(-1, 0).Value = part
    Application.ScreenUpdating = False
End Sub
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Okay, got this, after a run it does it's job but then it crashes, it goes into an error and selects line
rn=rng.row
as an error: Object variable or with block variable not set
Any ideas why and how to fix it? Thanks
Code:
Sub search()Application.ScreenUpdating = False
Dim rng As Range
Dim order As String
Dim rn As Long
Dim name As String
Dim county As String
Dim part As String
Dim desc As String
Dim active As Range
Dim ws As Worksheet
Windows("Weekly Plan.xls").Activate
Set active = ActiveCell
order = ActiveCell.Text
Windows("Plan_Report.xlsm").Activate
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Set rng = ActiveSheet.Columns("D:D").Find(What:=order, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    [B]rn = rng.Row[/B]
    name = ActiveWorkbook.ActiveSheet.Cells(rn, 5).Value
    county = ActiveWorkbook.ActiveSheet.Cells(rn, 12).Value
    part = ActiveWorkbook.ActiveSheet.Cells(rn, 6).Value
    desc = ActiveWorkbook.ActiveSheet.Cells(rn, 11).Value
    Windows("Weekly Plan 2018.xls").Activate
    active.Offset(-4, 0).Value = name
    active.Offset(-3, 0).Value = county
    active.Offset(-2, 0).Value = desc
    active.Offset(-1, 0).Value = part
    Application.ScreenUpdating = True
    Next ws
    Windows("Weekly_Plan.xls").Activate
End Sub
 
Last edited:
Upvote 0
Well did it with simple on error goto.. it did the trick, just still confused what the actual problem is..
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

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