obtain range from a cell on a find

[eds000]

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

 

[eds000]

I need help, doesn't anyone know how to get a range once you find the cell????? PLEASE HELP

 

[Joe4]

If "occurRange" is the range that the found cell is being set to, then you can get its address using:

occurRange.Address

 

[eds000]

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

 

[Joe4]

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.

[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]

 

[eds000]

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

 

[Joe4]

Look at your first line here:

[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:

[COLOR=#333333]Set occurRange.Address = .FindNext(occurRange)[/COLOR]

It needs to be:

[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.

MsgBox occurRange.Address

If you leave the property off, it defaults to ".Value", so

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.

 

[eds000]

thank you!

I found that once I added
occurRange = occurRange.address

it worked