Msgbox for reporting the value of a range....

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
hi,
i was wondering if anyone could point me in the right direction with this...

i have a dynamic range called Range1
i'm able to print out the address of the dynamic range....it shows as $z1:$Z5, then $z6:$Z20, etc.


within each range, the values are the same. How can i report the value of each range?

could i do msgbox Range1.value ? (that didn't work when i tried)
could i do msgbox Range1.address.value ? (that didn't work either)


help please :)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
If it's a range of values you want to output to the screen, you would have to use a loop of some type.

Example:
Code:
Dim cell As Range
For Each cell in Range("A1:B15")
   msgbox(cell.Value)
Next cell

Something like that. Does that help?
 

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
If you don't want it done for every cell in your range, and the cells all have the same value, then try this:
Code:
MsgBox Range(Left(Range1.Address, 4)).Value
This will display the value from the first cell in your range. HTH.
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
Thank you both.

i have a question for Martinee though...

what do "Left" and "4" mean in your equation?


Kev.
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169

ADVERTISEMENT

Could anyone tell me what the following code exactly means? Including what "Left" and "4" means. Please :)


MsgBox Range(Left(Range1.Address, 4)).Value
 

Theadish

New Member
Joined
Jan 21, 2005
Messages
34
I believe it's getting the first 4 values of the address starting from the left
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
Thank you very much.
I deleted the "left" and the "4" and the code did exactly what i needed. I think you were right Theadish....thank you


Also, thank you again Martinee
 

Forum statistics

Threads
1,148,050
Messages
5,744,513
Members
423,881
Latest member
Nguyen Vu

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
Top