使用VBA将单元格范围分配给变量数组 [英] Using VBA to assign range of cell values to array of variables

问题描述

我对VBA很新，在这里忍受。

我想为一组范围的值分配一组变量ie。运行一个简短的代码来简化以下

  Dim Sample 1 as string 
 Sample1 = activeworksheet.range（C17 ）.value 
 
 Dim Sample 2 as string 
 Sample2 = activeworksheet.range（C18）。value} 
  

等等

遵循excelfunctions.net教程，我知道我可以将声明缩短到

  Dim Sample（1 to 20）as a string 
  

但是教程放在那里（因为它是一个关于名称的教程），建议我填充如下

  sample（1）= activesheet.range（C7）。value 
 sample（2）= activesheet.range（C7）。value 
  / pre> 
 
 
等等
 
 
 
我发现下面的讨论是在正确的轨道上回答我的任务但是我无法将其应用到我的情况。 （循环的Excel VBA阵列范围）
 
 
 
作为后续笔记，我最终尝试为这些变量分配值以用于以下过程，而不是每次声明和分配它们。
 
 
 
谢谢！ 
解决方案
您应该：
 
 
 
 
 
定义要检索数据的范围
 
 
 
对于范围的每个单元格，检索数据
  dim tab（）作为字符串，单元格作为范围，我作为整数
i = 0 
 redim tab（0）
为ActiveWorksheet.Range中的每个单元格（C1： C20）
标签（i）=单元格
i = i + 1 
 redim保存标签（i）
下一个
  
 
 
 
 
 
 
编辑：我缩进代码正确显示
 
I'm very new to VBA, to bear with me here.

I want to assign a set of variables the value of a set of ranges ie. run a brief code to simplify the following
Dim Sample 1 as string
Sample1 = activeworksheet.range("C17").value

Dim Sample 2 as string
Sample2 = activeworksheet.range("C18").value}
and so on

Following an excelfunctions.net tutorial, I know that I can shorten the declaration to
Dim Sample(1 to 20) as a string
But the tutorial drops it there(because it's a tutorial about names), suggesting I populate it as follows
sample(1)=activesheet.range("C7").value
sample(2)=activesheet.range("C7").value
and so on

I found the discussion below to be on the right track to answer my quest, but I am having trouble applying it to my situation. (Excel VBA Array Ranges for a loop)

As a follow up note, I am ultimately trying to assign values to these variables for use in the following procedures, rather than declaring and assigning them each time.

Thanks! 
解决方案
You should :


Define the range you want to retrieve data
For each cell of the range, retrieve your datas
dim tab() As string, cell as range, i as integer
i = 0
redim tab(0)
for each cell in ActiveWorksheet.Range("C1:C20")
    tab(i) = cell
    i = i + 1
    redim preserve tab(i)
next



edit : I indent the code to display it correctly

                        
这篇关于使用VBA将单元格范围分配给变量数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！