Menu
I want to search a range A1-A99 in a certain sheet (wsCaseinfo) for the word Overview. I get a 1004 error on the 'with' line.
The code is part of a larger code using 3 different sheets in 2 different files. Code cycles through 100 files, so something efficient would be appreciated. Many thanks for your help.
With wsCaseinfo.Range(Cells(1, 1), Cells(99, 1))
Set cellx = .Find(what:="Overview", LookAt:=xlPart)
End With
591
Just right-click on the little arrows in the bottom-left corner of your workbook. You'll see a list of up to 15 worksheets in your workbook. If your workbook contains more that 15 sheets, click on More Sheets at the bottom of the list.
Find External Links using Edit Links OptionGo to the Data Tab. In the Connections group, click on Edit Links. It opens the Edit Links dialog box will list all the workbooks that are being referenced. Click on Break Links to convert all linked cells to values.
Go to the index sheet, and press Ctrl + F keys simultaneously to open the Find and Replace dialog box, type a keyword in the Find what box, and click the Find All button. See screenshot: Now all worksheet names containing the keywords are found and listed at the bottom of Find and Replace dialog box.
You need to append the Cells() with the parent sheet:
With wsCaseinfo.Range(wsCaseinfo.Cells(1, 1), wsCaseinfo.Cells(99, 1))
Other wise the Cells() will refer to the active sheet and not the same sheet as the Range().
You can also nest a With in the first With
With wsCaseinfo
With .Range(.Cells(1, 1), .Cells(99, 1))
Set cellx = .Find(what:="Overview", LookAt:=xlPart)
End With
End With
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
