Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "Range-Value" to variable?
Hi,
I need to extract the Range of an excel sheet into a variable. I want to get the "A1:C10" value as a string or as a list, if there are multiple selections. I have looked into the VBA manual of WinXP-Excel but I can't found this topic. VarRange = Worksheet("Sheet1").Range won't work. If somebody knows an answer, please give me a reply. sincerely A. Winter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "Range-Value" to variable?
dim MyData as Variant
MyData = Range("A1:C10") now you have an array (MyData) through which you can loop.. Sub Test() Dim MyData As Variant Dim rw As Long Dim cl As Long MyData = Range("A1:C10") For rw = LBound(MyData, 1) To UBound(MyData, 1) For cl = LBound(MyData, 2) To UBound(MyData, 2) ' do something with MyData(rw,cl) MsgBox MyData(rw, cl) Next cl Next rw End Sub Patrick Molloy Microsoft Excel MVP -----Original Message----- Hi, I need to extract the Range of an excel sheet into a variable. I want to get the "A1:C10" value as a string or as a list, if there are multiple selections. I have looked into the VBA manual of WinXP-Excel but I can't found this topic. VarRange = Worksheet("Sheet1").Range won't work. If somebody knows an answer, please give me a reply. sincerely A. Winter . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "Range-Value" to variable?
What do you mean by multiple selections? A1:C10 is multiple cells, but if
selected is a single area. Do you mean multiple areas (non contiguous cells selected). -- Regards, Tom Ogilvy "Andreas Winter" wrote in message om... Hi, I need to extract the Range of an excel sheet into a variable. I want to get the "A1:C10" value as a string or as a list, if there are multiple selections. I have looked into the VBA manual of WinXP-Excel but I can't found this topic. VarRange = Worksheet("Sheet1").Range won't work. If somebody knows an answer, please give me a reply. sincerely A. Winter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save "Range-Value" to variable?
Andreas,
I may misunderstand your question, but here is my suggestion anyway: Sub StoreRangeAddresses() 'DEclare variables Dim i As Long Dim arr() As String ReDim arr(1 To Selection.Areas.Count) As String 'Store addresses in array For i = 1 To Selection.Areas.Count arr(i) = Selection.Areas(i).Address(False, False) Next i 'Loop through array and display its contents For i = 1 To UBound(arr, 1) MsgBox arr(i) Next i End Sub Ture Magnusson Karlstad, Sweden "Andreas Winter" wrote in message om... Hi, I need to extract the Range of an excel sheet into a variable. I want to get the "A1:C10" value as a string or as a list, if there are multiple selections. I have looked into the VBA manual of WinXP-Excel but I can't found this topic. VarRange = Worksheet("Sheet1").Range won't work. If somebody knows an answer, please give me a reply. sincerely A. Winter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
using a "variable" in range names | Excel Discussion (Misc queries) |