C
CompleteNewb
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.
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.