在VBA For循环中引用范围

Question

感谢您提前提供的帮助！

我正在创建一个写入文本文件的宏。我在引用for循环内的范围时遇到问题。我有两个例子，其中我所称的范围返回一个"0"&"；。对于下面的第一张图像，用户可以在这里输入数据，方法是将数据输入到工作表中，然后使用一个按钮将其引导到一个用户表单，该表单会弹出用户输入的内容。在第一张图片中，用户由用户表单引导，数据被输出到另一张表单，我使用vlookup来显示信息。我这样做是为了在另一张纸上运行操作，只是为了帮助我跟踪所有事情，(新手vba用户(。第二张工作表是存储数据和运行计算的地方。

第一个图像，用户输入并显示第二个图像，数据存储和计算表

newDimName仅输出"_Cav"；以及存储在cavNum中用于循环的值。目的是根据用户输入的内容，将列A内的字符串与更多信息连接起来。我怀疑我没有正确引用列a的范围。Loop被设置为2；尺寸"；已由用户输入(lastUserDim中的值(。下面的代码包含一个工作表引用(不在代码段中(，因此它引用了正确的工作表。例"；对于Sheet2〃；，在循环之前；以"；在循环结束时。

Dim a As Integer, cavNum As Integer
Select Case numCav
Case Is = 4
For a = 2 To lastUserDim
For cavNum = 1 To 4
newDimName = Cells(a, 1) & "_Cav" & cavNum
Next cavNum
Next a
totalColumns = 4 * lastUserDim
Case Is = 8
For a = 2 To lastUserDim
For cavNum = 1 To 8
newDimName = Cells(a, 1) & "_Cav" & cavNum
Next cavNum
Next a
totalColumns = 8 * lastUserDim
Case Is = 16
For a = 2 To lastUserDim
For cavNum = 1 To 16
newDimName = Cells(a, 1) & "_Cav" & cavNum
Next cavNum
Next a
totalColumns = 16 * lastUserDim
Case Is = 32
For a = 2 To lastUserDim
For cavNum = 1 To 32
newDimName = Cells(a, 1) & "_Cav" & cavNum
Next cavNum
Next a
totalColumns = 32 * lastUserDim
Case Else
MsgBox "Please select what # of cavities this tool has."
End Select

对于下面的代码，我希望读取我定义的范围，并对于select case来确定单元格是否包含yes或no。循环旨在对用户输入的数据数量运行此操作。我再次怀疑我没有像以前在A列那样正确调用c列中的数据。我试着像第一个代码片段中那样使用单元格，只有range、range("(.value和range("(.text。

Dim LSLBound As String, LSLBoundRef As String
Dim c As Integer
For c = 2 To lastUserDim
LSLBoundRef = Range("C2:C" & c).Text 'ref to cell value to use for select case
Select Case LSLBoundRef
Case Is = "No"
LSLBound = "LBound 1;"
Case Is = "Yes"
'Do Nothing
End Select
Next c

Answer 1

如果第二个代码片段被包装在一个"对于Sheet2〃；，您需要在Range前面加一个句点，即**.**Range("C2"(……以便使用正确的表格。

Dim LSLBound As String, LSLBoundRef As String
Dim c As Integer
With Sheets("Sheet2")
For c = 2 To lastUserDim
LSLBoundRef = .Range("C2:C" & c).Text 'ref to cell value to use for select case
Select Case LSLBoundRef
Case Is = "No"
LSLBound = "LBound 1;"
Case Is = "Yes"
'Do Nothing
End Select
Next c
End With

计算实际引用的范围的一个好方法是使用Debug.Print range.address.将范围地址打印到即时窗口