:) i have my workbooks & worksheets straight now, thanks to jim!

i need to find a variable in a range, but without selecting the range,
i can't tell it to look after:activecell. i searched the newsgroup &
discovered using cells.count but it's not working. what am i doing
wrong (again)?

xxxxxxxxxxx
Sub ActualCopy()

LastRangeRow = wsMyInvoiceSheet.Cells(20000, 7).End(xlUp).Row

Set r = Range("g1:g" & LastRangeRow)

'find the program name in wsMyInvoiceSheet

r.Cells.Find(What:=myProgram, After:=r(r.Cells.Count),
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

<snip
End Sub
xxxxxxxxxxxxxxx

i'm getting my favorite run-time 91 error.

if i change the range to
Set r = wsMyInvoiceSheet.Range("g1:g" & LastRangeRow)
it bombs on the same "find" line, but with a different error message:
run time error 1004 - activate method of range class failed.

suggestions gratefully accepted! thanks
susan

oh.... one of the posts when i was searching said that "after" was not
optional.
so i will try taking it out. & the reason i narrowed the search area
instead of just the column was because i thought perhaps that was where
the problem was; so now i know i can put that back.

in THIS specific macro, it will always find what it is looking for, but
i didn't know how to go about an "if-it-doesn't-find-it" issue, so
thank you for explaining that, also.

see, i'm not looking to see IF it is there, what i really want to know
is where in the 300+-row spreadsheet the value IS. thinking out loud,
in that case i can probably use the rngFound.row address............
:D
many thanks for your help!
susan


Jim Thomlinson wrote:
When using find you don't need to be quite so explicit in the range you are
searching. Also note that After is an optional argument. The issue that I do
see is that the code will bomb out if you don't actually find what you are
looking for. Give this a whirl...

Sub ActualCopy()
Dim rngToSearch As Range
Dim rngFound As Range

Set rngToSearch = wsMyInvoiceSheet.Columns("G")

'find the program name in wsMyInvoiceSheet
Set rngFound = rngToSearch.Find(What:=myProgram, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rngFound Is Nothing Then
MsgBox "Sorry " & myProgram & " was not found."
Else
MsgBox rngFound.Address
End If
End Sub


--
HTH...

Jim Thomlinson


"Susan" wrote:

:) i have my workbooks & worksheets straight now, thanks to jim!

i need to find a variable in a range, but without selecting the range,
i can't tell it to look after:activecell. i searched the newsgroup &
discovered using cells.count but it's not working. what am i doing
wrong (again)?

xxxxxxxxxxx
Sub ActualCopy()

LastRangeRow = wsMyInvoiceSheet.Cells(20000, 7).End(xlUp).Row

Set r = Range("g1:g" & LastRangeRow)

'find the program name in wsMyInvoiceSheet

r.Cells.Find(What:=myProgram, After:=r(r.Cells.Count),
LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

<snip
End Sub
xxxxxxxxxxxxxxx

i'm getting my favorite run-time 91 error.

if i change the range to
Set r = wsMyInvoiceSheet.Range("g1:g" & LastRangeRow)
it bombs on the same "find" line, but with a different error message:
run time error 1004 - activate method of range class failed.

suggestions gratefully accepted! thanks
susan