LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 30
Default 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.

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Referring to value in range Forgone Excel Programming 0 June 24th 09 06:36 AM
Search Modules for Cell Addresses in Range Variables ExcelMonkey Excel Programming 0 December 17th 07 03:45 PM
Referring first row in range Asif Excel Programming 4 October 4th 07 02:26 AM
Need help with adding variables to cell addresses Matt[_33_] Excel Programming 14 October 11th 05 11:46 PM
referring to a range Kevin Smith[_3_] Excel Programming 2 September 7th 04 12:24 PM


All times are GMT +1. The time now is 04:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"