Using the Find method with formulas
 

I have a worksheet say Sheet1 that has values in column 1.

On Sheet 2 in column 1 I have formulas such as "=Sheet1!A1" etc.

I set the range to Worksheet("Sheet2").Range("A1:A1000").

The value 100 exist in column 1 of Sheet1.

I want to search the range for 100.

Set rRange = Worksheet("Sheet2").Range("A1:A1000")
rRange.Find(What:=100,LookIn:=xlValues,LookAt:=xlW hole)

Return Nothing.

Any suggestions?







*** Sent via Developersdex http://www.developersdex.com ***

Using the Find method with formulas
 

Edward,

Sub NewSub()
Dim rRange As Range
Dim myCell As Range

Set rRange = Worksheets("Sheet2").Range("A1:A1000")
Set myCell = rRange.Find(What:=100, LookIn:=xlValues, LookAt:=xlWhole)
If Not myCell Is Nothing Then
MsgBox myCell.Address & " has the value ""100"""
Else
MsgBox "Not Found"
End If
End Sub

HTH,
Bernie
MS Excel MVP



"Edward Ulle" wrote in message
...
I have a worksheet say Sheet1 that has values in column 1.

On Sheet 2 in column 1 I have formulas such as "=Sheet1!A1" etc.

I set the range to Worksheet("Sheet2").Range("A1:A1000").

The value 100 exist in column 1 of Sheet1.

I want to search the range for 100.

Set rRange = Worksheet("Sheet2").Range("A1:A1000")
rRange.Find(What:=100,LookIn:=xlValues,LookAt:=xlW hole)

Return Nothing.

Any suggestions?







*** Sent via Developersdex http://www.developersdex.com ***

Using the Find method with formulas
 

Bernie,

I have done as you suggested but it still doesn't work.

Dim rRange As Range
Dim rFind As Range

Set rRange = Worksheet("Sheet2").Range("A1:A1000")
Set rFind = rRange.Find(What:=100,LookIn:=xlValues,LookAt:=xlW hole)

As a matter of fact if you try to search from the menu for 100 on the
column of Sheet2 it says not found. I tied formulas, values without
success.

It seems the formulas "=Sheet1!Axxx" do not reveal the value. I am
using Office 2000 if that makes a difference.

Thanks.

Ed


*** Sent via Developersdex http://www.developersdex.com ***

Using the Find method with formulas
 

Ed,

What formatting do you have on your sheet? Remember, Excel uses the display value as the basis of
the search, not the actual value. So if your cell shows 100.00, you need to use

Set rFind = rRange.Find(What:="100.00", LookIn:=xlValues, LookAt:=xlWhole)

HTH,
Bernie
MS Excel MVP


"Edward Ulle" wrote in message ...
Bernie,

I have done as you suggested but it still doesn't work.

Dim rRange As Range
Dim rFind As Range

Set rRange = Worksheet("Sheet2").Range("A1:A1000")
Set rFind = rRange.Find(What:=100,LookIn:=xlValues,LookAt:=xlW hole)

As a matter of fact if you try to search from the menu for 100 on the
column of Sheet2 it says not found. I tied formulas, values without
success.

It seems the formulas "=Sheet1!Axxx" do not reveal the value. I am
using Office 2000 if that makes a difference.

Thanks.

Ed


*** Sent via Developersdex http://www.developersdex.com ***

Using the Find method with formulas
 

Bernie,

I am developing an application in Excel to use another applications
model data and perform post processing not available in the application.

I have discovered that as I have been developing this Excel VBA app, and
debugging coding errors, the workbook gets "corrupted".

I exported the entire project and reimported to a new workbook and now
it works.

Thanks for your help though.

Ed


*** Sent via Developersdex http://www.developersdex.com ***