FIND function

aberenguel

Board Regular
Joined
Aug 10, 2006
Messages
83
First off, here is the code I'm working with
Sub Load_Into_AutoSubPROCESS()

Range("N119:cb119").Select
Selection.Copy
Sheets("autsubPROCESS").Select
Range("A2").Select
Cells.Find(What:="@", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("VALT INPUT").Select
Range("N119").Select
End Sub

What I'm trying to do with this code is copy the selection from the VALT INPUT tab and paste it's values in the first available row in column A that contains "@" (within the autsubPROCESS tab). I want excel to look for "@" in column A only. Also I should note that the values in column A within the autsubPROCESS tab are coming from another tab (i.e. cell A2 = value from PIVOT tab cell A2)

For some reason I am recieving the following error message

Run-tme error '91':
Object variable or With block variable not set


Any ideas on why I'm recieving this error? It's beyond me!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
The Find statement will return an error if you try to activate a cell when the value is not found--there is nothing to activate.

Save the found range to a variable, and you can easily check whether or not the value was found.

For example:

Code:
Sub Load_Into_AutoSubPROCESS()
Dim rngPaste As Range

Range("N119:CB119").Copy

' if the value is not found, rngPaste will = Nothing
' otherwise, rngPaste will reference the first cell found containing the value
With Sheets("autsubPROCESS")
    Set rngPaste = .Cells.Find(What:="@", After:=.Range("A2"), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False)
End With

If Not rngPaste Is Nothing Then 'if the value was found
    'paste
    rngPaste.PasteSpecial xlValues
Else
    'otherwise...
End If

End Sub
 

aberenguel

Board Regular
Joined
Aug 10, 2006
Messages
83
Kristy,

I used the code you provided and when i step through it in break mode it doesn't seem to be working right. It returns no errors, but it doesn't seem to go to the autsubPROCESS tab to look for @.

When you say "save the found range to a variable" is the code within the WITH statement doing that? Or do i need to add additional code to do so?


thanks for your help!
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
You do not (usually) need to select cells/ranges in order to deal with them. This applies for copy/pasting--you can simply specify the range to copy and where to paste it to without needing to select any other tabs or ranges.

The code inside the With statement is referring to ranges on the "autsubPROCESS" sheet, and yes, it automatically searches for the "@" symbol and assigns that range to the rngPaste variable if it is found. You do not need to add anything :) Also, note that the sheet does not need to be active in order to use the Find statement on it.

I wasn't sure what you wanted to do if the value was *not* found, so I left the Else condition blank (in place of the "'otherwise... " comment).
 

aberenguel

Board Regular
Joined
Aug 10, 2006
Messages
83

ADVERTISEMENT

We use an [autosub] report that lists all our contracts and thier asset amount. Then we compare the list of contracts to the list of contracts we have in our database. If there are any contracts that are in the database but not on the autosub report The VALT INPUT tab will display such contracts.

the autsubPROCESS tab is populated by a pivot table on a different tab. THe pivot table contains the information from the autosub report.

There should always be an @ symbol in column A for excel to find on the autsubPROCESS tab. But for some reason the code is not finding it.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
There should always be an @ symbol in column A for excel to find on the autsubPROCESS tab. But for some reason the code is not finding it.

Maybe you need to change the Find statement a bit? Currently you are searching formulas as well as all of the cells (not just column A). How about trying this instead?

Code:
Set rngPaste = .Columns("A:A").Find(What:="@", LookIn:=xlValues, LookAt:=xlPart

This should search the values in column A of the sheet.
 

Forum statistics

Threads
1,143,672
Messages
5,720,217
Members
422,270
Latest member
CaptainMurray

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
Top