选择带有变量的工作表时，Excel 2007 VBA 运行时错误

Excel 2007 VBA runtime error when selecting worksheet with variable

提问by mattnhugh

I've been recently getting back into VBA and have been testing out a concept of Adding Worksheets by the string in a variable. I've been able to get the worksheets to add successfully and the last two lines of my code are simply to select the sheet name of the variable and then select cell A + the row number that is stored in another variable

我最近重新使用 VBA，并一直在测试通过变量中的字符串添加工作表的概念。我已经能够成功添加工作表，我的代码的最后两行只是选择变量的工作表名称，然后选择单元格 A + 存储在另一个变量中的行号

In Sheet1, Column A, starting in A1, I have a list of 8 different names that cycles through:

在 Sheet1 的 A 列中，从 A1 开始，我有一个包含 8 个不同名称的列表，这些名称循环显示：

• Bob
• Jeff
• Max
• Steve
• Rosie
• Pippa
• Penelope
• Rob

• 鲍勃
• 杰夫
• 最大限度
• 史蒂夫
• 罗茜
• 皮帕
• 佩内洛普
• 抢

I am expecting the macro to end on Sheet "Rob" with cell A9 selected, however i get a runtime 1004 error

我希望宏在选中单元格 A9 的工作表“Rob”上结束，但是我收到运行时 1004 错误

I have stepped through the code and it is selecting the sheet correctly with the variable, but when it tries to select row A9, the error presents

我已经逐步完成了代码，它正在使用变量正确选择工作表，但是当它尝试选择 A9 行时，出现错误

My code is below:

我的代码如下：

Sub Add_worksheets()

Dim sheetName As String
Dim rownum As Integer

rownum = 1
Range("A" & rownum).Select
sheetName = ActiveCell.Value

Do Until Range("A" & rownum).Value = ""
Range("A" & rownum).Select
sheetName = CStr(ActiveCell.Value)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName
Sheets("sheet1").Select
rownum = rownum + 1
Loop

Sheets(sheetName).Select
Range("A" & rownum).Select

End Sub

Any help would be greatly appreciated

任何帮助将不胜感激

采纳答案by bonCodigo

I also suggest you add if-elseto check if the sheet name exists.. Why do you want to select? I do not support selections, since you need it you may try this: Because when you are incrementing rownumit goes to 10th cell, that means, Sheetname no longer has a name but empty..

我还建议您添加if-else以检查工作表名称是否存在.. 为什么要这样做select？我不支持选择，因为你需要它，你可以试试这个：因为当你增加rownum它到第 10 个单元格时，这意味着 Sheetname 不再有名称而是空的..

Dim sheetName As String
Dim rownum As Integer
Dim WS as WorkSheet

rownum = 1
sheetName = Range("A" & rownum).Value '-- remove select


Do Until Range("A" & rownum).Value = ""
Range("A" & rownum).Select
sheetName = CStr(ActiveCell.Value)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName
Set WS = Sheets(sheetName)
Sheets("sheet1").Select
rownum = rownum + 1
Loop

rownum = rownum - 1 '-- here
WS.Activate
Range("A" & rownum).Value = rownum

Or you may just use an array to do the creation of the sheets and then selecta sheet later....Instead of going back and forth to first sheet...with names..

或者您可以只使用一个数组来创建工作表，然后稍后选择一张工作表.​​.....而不是来回切换到第一张工作表.​​.....带有名称......

Sub addSheets()
Dim rowNum as Integer
Dim Ws as WorkSheet
Dim vArray as Variant

'-- creates one dimensional array
vArray = WorkSheetFunction.Transpoe(Sheets(1).Range("A1:A9")) 

For rownum = Lbound(vArray) to Ubound(vArray)
   If IsEmpty(vArray(rowNum)) Then
      On Error Resume Next
      Set Ws = Sheets(vArray(rowNum))
      If Err.Number <> 0 Then '--no such sheet, so add it
         Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = vArray(rowNum)
         Set Ws = Sheets(vArray(rowNum))
      End If
   End IF
Next rownum

Ws.Activate
Ws.Range("A1").offset(rownum).value = rownum
End Sub