obtain range from a cell on a find

eds000

Board Regular
Joined
Dec 11, 2006
Messages
76
I first need to find a value in a cell and then store the range of that cell.
the below code in VBA stores the email NOT the range of the cell.
thank you for any help - Eileen

Dim occurRange As Range

Windows(CARDfile).Activate 'cardholder summary file
With Range("Z:Z") 'EMAIL
Do Until X > occurCNT 'x=1 to start
If X = 1 Then
Set occurRange = .Find(What:=emailCK, LookIn:=xlValues, LookAt:=xlWhole) 'stores the range
Else
Set occurRange = .FindNext(occurRange)
End If 'x=1

If occurRange.Offset(0, -21).Value = " " Then 'active status - don't look anymore
cStatus = "A" 'A=active
X = occurCNT 'Range.Offset(RowOffset, ColumnOffset)
Else
cStatus = occurRange.Offset(0, -21).Value 'store last status value unless found active
End If
X = X + 1
Loop 'X loop
End With
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I need help, doesn't anyone know how to get a range once you find the cell????? PLEASE HELP
 
Upvote 0
If "occurRange" is the range that the found cell is being set to, then you can get its address using:
Code:
occurRange.Address
 
Upvote 0
I get a Compile error: Assignment to constant not permitted. - when I changed it to: occurRange.Address OR Set OccurRange.Address


If X = 1 Then
occurRange.Address = .Find(What:=emailCK, LookIn:=xlValues, LookAt:=xlWhole) 'stores the range
Else
occurRange.Address = .FindNext(occurRange)
End If 'x=1
 
Upvote 0
No, you don't change that. occurRange is a range variable. .Address returns a string. .Find returns a range, so you cannot set it equal to a string data type.
You use .Address to get the address AFTER that part has run and found your value, i.e.
Code:
[COLOR=#333333]If X = 1 Then[/COLOR]
[COLOR=#333333]    occurRange = .Find(What:=emailCK, LookIn:=xlValues, LookAt:=xlWhole) 'stores the range[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]    occurRange = .FindNext(occurRange)[/COLOR]
[COLOR=#333333]End If 'x=1
[/COLOR][COLOR=#ff0000]MsgBox "Found value in cell: " & occurRange.Address[/COLOR]
 
Last edited:
Upvote 0
I am confused - when the macro is running I look at what is stored in occurRange and it is the email address which is the value in the cell; I want the range.

If I remove the SET then I get the following error: Run-time error'91': Object variable or with block variable not set

Dim occurRange As Range
With Range("Z:Z") 'EMAIL
Do Until X > occurCNT 'x=1 to start
If X = 1 Then
Set occurRange = .Find(What:=emailCK, LookIn:=xlValues, LookAt:=xlWhole) 'stores the range
Else
Set occurRange.Address = .FindNext(occurRange)
End If 'x=1

If occurRange.Offset(0, -21).Value = " " Then 'active status - don't look anymore
cStatus = "A" 'A=active
X = occurCNT 'Range.Offset(RowOffset, ColumnOffset)
Else
cStatus = occurRange.Offset(0, -21).Value 'store last status value unless found active
End If
X = X + 1
Loop 'X loop
End With
 
Upvote 0
Look at your first line here:
Code:
[COLOR=#333333]Dim occurRange As Range[/COLOR]
So you are declaring "occurRange" to be a Range variable.
When setting a range object, you must use the "Set" keyword.

However, .Address is a property of a range. And .Find returns a Range. So, this statement is not correct:
Code:
[COLOR=#333333]Set occurRange.Address = .FindNext(occurRange)[/COLOR]
It needs to be:
Code:
[COLOR=#333333]Set occurRange = .FindNext(occurRange)[/COLOR]
Once the range is set to something, then you can use properties like ".Value" to return the value, or ".Address" to return the range, i.e.
Code:
MsgBox occurRange.Address
If you leave the property off, it defaults to ".Value", so
Code:
MsgBox occurRange
simply returns the value from that cell.

You need to write that block of code like I showed you in my previous post. You removed the ".Address" from the first of your "Set occurRange" lines, but not the second.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

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