vba 创建一个基于用户在 Excel 中输入的大小的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11363577/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a table that is sized based on user input in Excel?
提问by TheTreeMan
For the record, I'm using Excel 2010.
作为记录，我使用的是 Excel 2010。
I'm trying to teach myself VBA for a job at work (they give the interns the VBA stuff...), and I'm getting stuck on the simplest of things.
我正在尝试自学 VBA 以应对工作中的工作（他们给实习生提供 VBA 的东西......），但我陷入了最简单的事情。
I am trying to make a bordered table that is three columns wide. The amount of rows in the table has to be based on the amount of inputs the user has. In my code, that is the value given for Countby the user.
我正在尝试制作一个三列宽的带边框的表格。表中的行数必须基于用户的输入量。在我的代码中，这是Count用户给出的值。
My issue is that I have no idea how to select the range I need. The only way I know how to select a range is using:
我的问题是我不知道如何选择我需要的范围。我知道如何选择范围的唯一方法是使用：
ActiveCell.Range("Top left cell:Bottom right cell").Select
If it were from A1to C8, how do I make that work? I wish it would just work like:
如果它是来自A1于C8，我如何使这项工作？我希望它可以像这样工作：
ActiveCell.Range("A1:C(count)").Select
This is what I have so far:
这是我到目前为止：
Option Explicit
Dim Count As Long
Dim CFLArray() As Variant
Sub TableCreation1()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Time (days)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "CFL (measured)"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "De (estimated)"
ActiveCell.Offset(0, -2).Range("A1:C1").Select
Selection.Font.Bold = True
ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0,1).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0,2).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Select
End Sub
Sub FindRange()
Range("A2").Select
Count = InputBox("How many pairs of data do you have? ")
End Sub
I taught myself VBA a day or so ago using a book my work had, but I couldn't find anything along these lines in there. My internet searches failed as well. The worst part is I know it's going to be incredibly simple.
一天左右前，我使用我工作的一本书自学了 VBA，但我在那里找不到任何类似的内容。我的互联网搜索也失败了。最糟糕的是我知道这将非常简单。
采纳答案by Gaffi
It is that simple! You were just off by a little bit.
就是这么简单！你只是走了一点点。
Change: ActiveCell.Range("A1:C(count)").Select
改变： ActiveCell.Range("A1:C(count)").Select
To: ActiveCell.Range("A1:C" & count).Select
到： ActiveCell.Range("A1:C" & count).Select
Though, I don't see any countvariable in your code.
不过，我count在您的代码中没有看到任何变量。
If you know that it's always the same column, you could do this:
如果您知道它始终是同一列，则可以执行以下操作：
Range("A1:C" & ActiveSheet.Range("C1048576").End(xlUp).Row).Select
Range("A1:C" & ActiveSheet.Range("C1048576").End(xlUp).Row).Select
(1048576is the max row in Excel 2010. This may not be perfect for your data, but hopefully it will get you started in the right direction...)
（1048576是 Excel 2010 中的最大行。这可能不适合您的数据，但希望它能让您朝着正确的方向开始......）
Also, just as an added point about VBA, you don't need to use Selectin most cases. For example, this:
另外，就像关于 VBA 的一个附加点一样，Select在大多数情况下您不需要使用。例如，这个：
Range("A1").Select
ActiveCell.FormulaR1C1 = "Time (days)"
Can be rewritten more simply as:
可以更简单地改写为：
Range("A1").FormulaR1C1 = "Time (days)"
And if you are just using text:
如果您只是使用文本：
Range("A1").value = "Time (days)"
I also want to point out that using ActiveCell.Range("A1:C" & count).Selectis not the same as ActiveSheet.Range("A1:C" & count).Select. Depending on what cell is currently selected, these will select different ranges. If you really mean A1:C8, and not A1:C8 (relative to the current cell), then you'll want to use the ActiveSheet(or omitted) version.
我还想指出，使用ActiveCell.Range("A1:C" & count).Select与ActiveSheet.Range("A1:C" & count).Select. 根据当前选择的单元格，这些将选择不同的范围。如果您的意思是A1:C8, 而不是A1:C8 (relative to the current cell)，那么您将要使用ActiveSheet（或省略）版本。
回答by assylias
That would be:
那将是：
ActiveCell.Range("A1:C" & Count).Select
The &is the string concatenation operator.
该&是字符串连接运算符。
Side note - your first procedure (TableCreation1) could be rewritten more concisely:
旁注 - 您的第一个过程（TableCreation1）可以更简洁地重写：
Sub TableCreation1()
Range("A1") = "Time (days)"
Range("B1") = "CFL (measured)"
Range("C1") = "De (estimated)"
Range("A1:C1").Font.Bold = True
Columns("A:C").EntireColumn.EntireColumn.AutoFit
End Sub
