Find function bypass
I don't like relying on the activesheet of a different workbook.
If you know the name of the worksheet in toys.xls, you might as well specify it:
Dim FoundCell As Range
Dim ToysWks As Worksheet
Set ToysWks = Workbooks("Toys.xls").Worksheets("somesheetnameher e")
Set FoundCell = Cells.Find(What:="Toys", After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If FoundCell Is Nothing Then End
'not found, do nothing or whatever you want
MsgBox "Toys not found!"
Else
ToysWks.Range("a23").Value = FoundCell.End(xlToRight).Value
End With
carl wrote:
Hi Dave,
Thanks for your help. Being a bit of a beginner I can't get this to work.
I changed it because I assume I couldn't just copy and paste it in to my
script. Either way it's not worked. I changed it to this:
With Cells.Find(What:="Toys", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Dim FoundCell As Range
Set FoundCell = Cells.Find(Toys) 'no .activate here!
If FoundCell Is Nothing Then End
'not found, do nothing or whatever you want
Else
Selection.End(xlToRight).Select
ActiveCell.Select
Selection.Copy
Windows("Toys.xls").Activate
Range("A23").Select
Selection.PasteSpecial Paste:=xlPasteValues
'found it, do the real work.
End With
It stops at the "Else" part saying that it needs "If" after "Else". Am I
doing this all wrong?
"Dave Peterson" wrote:
Dim FoundCell as range
set foundcell = cells.find(....) 'no .activate here!
if foundcell is nothing then
'not found, do nothing or whatever you want
else
'found it, do the real work.
end if
Dave Peterson
--
Dave Peterson
|