VBA Find Method

p2jh

New Member
Joined
Nov 5, 2003
Messages
4
Hello,


I'm currenlty having problems getting the VBA Range.Find method. What I want in to find a number in the given range...however the values in the range are all being determined by formulas (don't know if that is causing the problem)...

Anyway, the current statement I'm using is...
Set foundCell = FuncAreaRange.Find(dblMax,Lookin:=xlValues)

where foundCell and FuncAreaRange are Range Variables and dblMax is a double variable...

Any ideas what I could be doing wrong? The statement currently returns Nothing..

Thanks in advance for any help!

ph
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
If you don't mind changing the selected cell:

FuncAreaRange.Select
Selection.Find(What:=dblMax, LookIn:=xlValues).Select
Set Foundcell = Selection

For a Reason I don't know,

This works:
Range("A1:A10").Select
Selection.Find(What:=dblMax, LookIn:=xlValues).Select

This gives an error:
Range("A1:A10").Find(What:=dblMax, LookIn:=xlValues).Select
 
Upvote 0
I duplicated your code with no problems. Some things to check:

1. Is your range referring to the correct sheet?
2. Are you sure the value you are searching for is exactly the same?

Both will cause a return of Nothing.

K
 
Upvote 0
kkknie said:
I duplicated your code with no problems. Some things to check:

1. Is your range referring to the correct sheet?
2. Are you sure the value you are searching for is exactly the same?

Both will cause a return of Nothing.

K

Yes, didn't tried with it exactly before, but it does work.

Thought it was because of the .Find cannot be used with a variable Range, only Selection (as the example I posted before, with Range("A1:A10")) But think I've done something wrong the first time since it seems to work now...

EDIT: Tried AGain, it wasn't working for real because the first time I had the "After:=ActiveCell," part in the .Find, so that is why I got an error....
 
Upvote 0
I'm really new to this whole VBA sub/function/range etc and am trying to use the Find function.

I'm looking for the EXACT contents for a cells which I know exists.

I'm not sure if a function must have an input or if I should use a sub and I do not understand how the Find Method really does work. I've tried finding examples and explanations, but still do not understand. Code is below. Please help.:p

<code>
Public Function findCell() As Range
Dim SearchRange As Range
Dim FoundCell As Range
Dim FindWhat As Variant
Dim MatchCase As Boolean
Dim LookIn As XlFindLookIn
Dim LooAt As XlLookAt
Dim SearchOrder As XlSearchOrder
Set SearchRange = ThisWorkbook.Sheets(1).Range("A5:IV65536")
Set FindWhat = "Setting temp."
Set LookIn = xlValues
Set LookAt = xlPart
Set SearchOrder = xlByRows
Set MatchCase = False
Debug.Print FindWhat.Text
Set FoundCell = SearchRange.Find(FindWhat:=FindWhat, _
LookIn:=LookIn, LookAt:=LookAt, SearchOrder:=SearchOrder, MatchCase:=MatchCase)
MsgBox FoundCell.Text

End Function
</code>
 
Upvote 0
I've update the code on this a bit and I want to return a specific cell. However, the error message I recieve is 'Run Time error 91' Object variable or with block Variable not set

The Debug highlights the line starting with "Set FoundCell...."

<code>Public Function findCell() As Range
Dim SearchRange As Range
Dim FoundCell As Range
Dim FindWhat As Variant
Dim MatchCase As Boolean
Dim LookIn As XlFindLookIn
Dim LooAt As XlLookAt
Dim SearchOrder As XlSearchOrder
Set SearchRange = ThisWorkbook.Sheets(1).Range("A5:IV65536")
FindWhat = "S"
LookIn = xlValues
LookAt = xlPart
SearchOrder = xlByColumns
MatchCase = False
Set FoundCell = SearchRange.Find(FindWhat, , LookIn, LookAt, SearchOrder, xlNext, MatchCase, , SearchFormat:=False)

End Function</code>
 
Upvote 0
I know this is an old post but, just in case someone else has a similar query, could the problem be misspelling in;
<code>Dim LooAt As XlLookAt</code>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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