Referring to Range addresses when in variables
 

This is very frustrating, and I can't find any web references to msgboxing
actual ranges, not their contents. Plus, in trying, by process of
elimination, to find out the solution, it seems nothing presents itself

For the below code (and thanks to help from you guys, it works), I have
already set the FoundIt variable to be the value in a .find function; this
is working, and I can see the value in step-through.

StartRange = shtRaw.Range(FoundIt.Address).Offset(3, -2).Address
EndRange = shtRaw.Range(StartRange).End(xlDown).Address

Here's what I found weird; I can msgbox StartRange and EndRange, and I get
it (the box shows, for instance, $A$3).

Now, in a different place, I have this (ElementList is an array I've
populated, and it does have its values; FoundIt IS getting set through the
loop; I can see the values change each loop; CompListRng is a previosuly
specified range):

For i = LBound(ElementList) To UBound(ElementList)
FoundIt = CompListRng.Find(ElementList(i), LookIn:=xlValues, LookAt:=xlPart)
Set FoundWhere = FoundIt.Address
MsgBox FoundIt & " " & FoundWhere
Next i

For the FoundWhere part, I always error out; it seems to be the way I'm
referring to the range or something. Now, I have tried to msgbox the range
address of the cell that FoundIt got found in MANY ways; dimming the
variable as a range first (even though StartRange from above was never
dimmed), not dimming it, trying Range(FoundIt).Address,
Range(FoundIt.Address).Address, trying tro msgbox it directly without
setting it to a variable first, using the sheet variable first (as seen in
the StartRange above), etc. Why, when I can msgbox the cell address in
StartRange no problem, can I not msgbox the cell address of FoundIt in this
instance, which to me looks very much the same? For every value in
ElementList, I'm finding it in the CompListRng, and I'm just trying to
msgbox what was found (which works when I remove the address part), and the
cell address (like $A$9).

Thanks for any help. I hope I didn't miss something completely obvious.

Referring to Range addresses when in variables
 

On Oct 14, 12:53*pm, "CompleteNewb" wrote:
This is very frustrating, and I can't find any web references to msgboxing
actual ranges, not their contents. *Plus, in trying, by process of
elimination, to find out the solution, it seems nothing presents itself

For the below code (and thanks to help from you guys, it works), I have
already set the FoundIt variable to be the value in a .find function; this
is working, and I can see the value in step-through.

StartRange = shtRaw.Range(FoundIt.Address).Offset(3, -2).Address
EndRange = shtRaw.Range(StartRange).End(xlDown).Address

Here's what I found weird; I can msgbox StartRange and EndRange, and I get
it (the box shows, for instance, $A$3).

Now, in a different place, I have this (ElementList is an array I've
populated, and it does have its values; *FoundIt IS getting set through the
loop; I can see the values change each loop; CompListRng is a previosuly
specified range):

For i = LBound(ElementList) To UBound(ElementList)
FoundIt = CompListRng.Find(ElementList(i), LookIn:=xlValues, LookAt:=xlPart)
Set FoundWhere = FoundIt.Address
MsgBox FoundIt & " *" & FoundWhere
Next i

For the FoundWhere part, I always error out; it seems to be the way I'm
referring to the range or something. *Now, I have tried to msgbox the range
address of the cell that FoundIt got found in MANY ways; dimming the
variable as a range first (even though StartRange from above was never
dimmed), not dimming it, trying Range(FoundIt).Address,
Range(FoundIt.Address).Address, trying tro msgbox it directly without
setting it to a variable first, using the sheet variable first (as seen in
the StartRange above), etc. *Why, when I can msgbox the cell address in
StartRange no problem, can I not msgbox the cell address of FoundIt in this
instance, which to me looks very much the same? *For every value in
ElementList, I'm finding it in the CompListRng, and I'm just trying to
msgbox what was found (which works when I remove the address part), and the
cell address (like $A$9).

Thanks for any help. *I hope I didn't miss something completely obvious..

Don't use Set. Instead of:
Set FoundWhere = FoundIt.Address
use:
FoundWhere = FoundIt.Address

Referring to Range addresses when in variables
 

Thanks, James.

Am I right that using "set" creates a range OBJECT as opposed to an actual
range address? Is that the difference? Because I am CONSTANTLY running into
errors because I'm referring to cell addresses, ranges, etc. as variables,
and then changing the method of referring to them until I stop getting
errors, and I still have a hard time figuring out exactly what the
difference is.

Is there a page somewhere on the web that talks about making ranges
variables, referring to ranges using cells., range(, .address, .value, etc.?
I'm doing all kinds of setting a variable to be a starting range address,
then another one for an ending range address, then another one that's the
Range(Starting, Ending), or sometimes doing the whole Range("A:" & and then
concatenating other cell addresses, then referencing all these different
ways to resize, find values, etc., and I'm running into a lot of dumb
problems. And I don't mean the problems are dumb, I mean I'M dumb.

Thanks again for the help



"James Ravenswood" wrote in message
...
On Oct 14, 12:53 pm, "CompleteNewb" wrote:
This is very frustrating, and I can't find any web references to msgboxing
actual ranges, not their contents. Plus, in trying, by process of
elimination, to find out the solution, it seems nothing presents itself

For the below code (and thanks to help from you guys, it works), I have
already set the FoundIt variable to be the value in a .find function; this
is working, and I can see the value in step-through.

StartRange = shtRaw.Range(FoundIt.Address).Offset(3, -2).Address
EndRange = shtRaw.Range(StartRange).End(xlDown).Address

Here's what I found weird; I can msgbox StartRange and EndRange, and I get
it (the box shows, for instance, $A$3).

Now, in a different place, I have this (ElementList is an array I've
populated, and it does have its values; FoundIt IS getting set through the
loop; I can see the values change each loop; CompListRng is a previosuly
specified range):

For i = LBound(ElementList) To UBound(ElementList)
FoundIt = CompListRng.Find(ElementList(i), LookIn:=xlValues,
LookAt:=xlPart)
Set FoundWhere = FoundIt.Address
MsgBox FoundIt & " " & FoundWhere
Next i

For the FoundWhere part, I always error out; it seems to be the way I'm
referring to the range or something. Now, I have tried to msgbox the range
address of the cell that FoundIt got found in MANY ways; dimming the
variable as a range first (even though StartRange from above was never
dimmed), not dimming it, trying Range(FoundIt).Address,
Range(FoundIt.Address).Address, trying tro msgbox it directly without
setting it to a variable first, using the sheet variable first (as seen in
the StartRange above), etc. Why, when I can msgbox the cell address in
StartRange no problem, can I not msgbox the cell address of FoundIt in
this
instance, which to me looks very much the same? For every value in
ElementList, I'm finding it in the CompListRng, and I'm just trying to
msgbox what was found (which works when I remove the address part), and
the
cell address (like $A$9).

Thanks for any help. I hope I didn't miss something completely obvious.

Don't use Set. Instead of:
Set FoundWhere = FoundIt.Address
use:
FoundWhere = FoundIt.Address