I have done a small project, which consists of 5 excel sheet in, code is working fine and I am getting exact result also, but if I rename sheets from sheet1 to some other name I am getting Subscript out of range Error.
我做了一个小项目，其中包含5个Excel工作表，代码工作正常，我也得到了确切的结果，但如果我将工作表从sheet1重命名为其他名称，我将获得Subscript超出范围错误。
What is the reason for this and what needs to be done to overcome this. Please help.
这是什么原因以及需要采取什么措施来克服这个问题。请帮忙。
Below is the code
以下是代码
Public Sub amount_final()
Dim Row1Crnt As Long
Dim Row2Crnt As Long
With Sheets("sheet4")
Row1Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
Row1Crnt = 2
With Sheets("sheet3")
Row2Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
5 个解决方案
#1
12
There is nothing wrong with the code per se. You will get Subscript out of range error if Excel is not able to find a particular sheet which is quite obvious since you renamed it. For example, if you rename your sheet "Sheet3" to "SheetXYZ" then Excel will not be able to find it.
代码本身没有任何问题。如果Excel无法找到自重命名以来非常明显的特定工作表，则会导致下标超出范围错误。例如，如果将工作表“Sheet3”重命名为“SheetXYZ”，则Excel将无法找到它。
The only way to avoid these kind of errors is to use CODENAME of the sheets. See Snapshot
避免这些错误的唯一方法是使用工作表的CODENAME。请参见快照
Here we have a sheet which has a name "Sample Name before Renaming"
这里有一张名为“重命名前的样品名称”的表格
So consider this code
所以请考虑这段代码
Sheets("Sample Name before Renaming").Range("A1").Value = "Blah Blah"
The same code can be written as
相同的代码可以写成
Sheet2.Range("A1").Value = "Blah Blah"
Now no matter how many times you rename the sheet, the above code will always work :)
现在，无论您重命名工作表多少次，上面的代码将始终有效:)
HTH
HTH
Sid
希德
#2
1
The basic issue is that you are referring to sheets using their common names and not their codenames. Whenever you refer to Sheets("sheet4"), you are relying on the sheet having that name in Excel. Codenames are the names assigned in Visual Basic so the end user does not interact with them/as a developer you can change the Excel names any time you like
基本问题是您使用通用名称而不是其代号来表示工作表。每当您引用表格（“sheet4”）时，您依赖于Excel中具有该名称的表格。 Codenames是在Visual Basic中分配的名称，因此最终用户不与它们交互/作为开发人员，您可以随时更改Excel名称
Using code names is covered at around 9:40 in this Excel help video. You'll note they are quicker to type than the Excel names as do not require the 'Sheets()' qualifier
在此Excel帮助视频中，大约9:40将使用代码名称。您会注意到它们的输入速度比Excel名称更快，因为不需要'Sheets（）'限定符
I couldn't see Sheets("Sheet1") in your code sample but you can switch to codenames for all sheets very quickly by finding/replacing all examples of e.g. 'Sheets("Sheet2").' with 'Sheet2.'
我无法在您的代码示例中看到Sheets（“Sheet1”），但您可以通过查找/替换所有示例来快速切换到所有工作表的代号。 '表（ “Sheet 2中”）。'用'Sheet2'。
#3
0
Refer to each sheet by their code names instead. They are set to Sheet1, Sheet2 etc as default, but you can rename them in the Properties window for each sheet if you want. This way you can write your code like below instead, regardless of what you name the sheets.
请参考每个表单的代码名称。它们默认设置为Sheet1，Sheet2等，但您可以根据需要在“属性”窗口中为每个工作表重命名它们。这样，无论您为工作表命名，都可以像下面那样编写代码。
With Sheet1
Row1Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
Row1Crnt = 2
With Sheet2
Row2Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
etc...
#4
0
I wanted to share my experience battling this problem. Here is the mistake I committed:
我想分享我与这个问题作斗争的经验。这是我犯的错误：
Dim DailyWSNameNew As String
lastrow = Sheets("DailyWSNameNew").Range("A65536").End(xlUp).Row + 1 -- This is wrong as I included a placeholder worksheet name in quotes
Correction:
更正：
lastrow = Sheets(DailyWSNameNew).Range("A65536").End(xlUp).Row + 1
This solved it.
这解决了它。
#5
0
I encountered this error earlier today but could not use any solution above, I did however eventually managed to solve it myself.
我今天早些时候遇到了这个错误但是无法使用上面的任何解决方案，但我最终设法自己解决了这个问题。
My situation was that I had a list contained in column A. For each cell with a value I stored the value in a variable, created a new sheet and named the sheet according to the value stored in the variable.
我的情况是我在列A中有一个列表。对于每个具有值的单元格，我将值存储在一个变量中，创建一个新的工作表并根据存储在变量中的值命名工作表。
A bit later in the code I tried to select the newly created sheet by using the code:
稍后在代码中我尝试使用代码选择新创建的工作表：
Sheets(ValueVariable).Select
I encountered the "Subscript out of range" error and I couldn't figure out why. I've used similar code before with success. I did however solve it by casting the variable as a string. Declaring the variable as a string did not seem to work for me.
我遇到了“下标超出范围”错误，我无法弄清楚原因。我成功之前使用过类似的代码。然而，我确实通过将变量转换为字符串来解决它。将变量声明为字符串似乎对我不起作用。
So, if anyone else encounter this error and want something to try, perhaps this will work for you:
所以，如果其他人遇到这个错误并想要尝试一些东西，也许这对你有用：
Sheets(Cstr(ValueVariable)).Select
#1
12
There is nothing wrong with the code per se. You will get Subscript out of range error if Excel is not able to find a particular sheet which is quite obvious since you renamed it. For example, if you rename your sheet "Sheet3" to "SheetXYZ" then Excel will not be able to find it.
代码本身没有任何问题。如果Excel无法找到自重命名以来非常明显的特定工作表，则会导致下标超出范围错误。例如，如果将工作表“Sheet3”重命名为“SheetXYZ”，则Excel将无法找到它。
The only way to avoid these kind of errors is to use CODENAME of the sheets. See Snapshot
避免这些错误的唯一方法是使用工作表的CODENAME。请参见快照
Here we have a sheet which has a name "Sample Name before Renaming"
这里有一张名为“重命名前的样品名称”的表格
So consider this code
所以请考虑这段代码
Sheets("Sample Name before Renaming").Range("A1").Value = "Blah Blah"
The same code can be written as
相同的代码可以写成
Sheet2.Range("A1").Value = "Blah Blah"
Now no matter how many times you rename the sheet, the above code will always work :)
现在，无论您重命名工作表多少次，上面的代码将始终有效:)
HTH
HTH
Sid
希德
#2
1
The basic issue is that you are referring to sheets using their common names and not their codenames. Whenever you refer to Sheets("sheet4"), you are relying on the sheet having that name in Excel. Codenames are the names assigned in Visual Basic so the end user does not interact with them/as a developer you can change the Excel names any time you like
基本问题是您使用通用名称而不是其代号来表示工作表。每当您引用表格（“sheet4”）时，您依赖于Excel中具有该名称的表格。 Codenames是在Visual Basic中分配的名称，因此最终用户不与它们交互/作为开发人员，您可以随时更改Excel名称
Using code names is covered at around 9:40 in this Excel help video. You'll note they are quicker to type than the Excel names as do not require the 'Sheets()' qualifier
在此Excel帮助视频中，大约9:40将使用代码名称。您会注意到它们的输入速度比Excel名称更快，因为不需要'Sheets（）'限定符
I couldn't see Sheets("Sheet1") in your code sample but you can switch to codenames for all sheets very quickly by finding/replacing all examples of e.g. 'Sheets("Sheet2").' with 'Sheet2.'
我无法在您的代码示例中看到Sheets（“Sheet1”），但您可以通过查找/替换所有示例来快速切换到所有工作表的代号。 '表（ “Sheet 2中”）。'用'Sheet2'。
#3
0
Refer to each sheet by their code names instead. They are set to Sheet1, Sheet2 etc as default, but you can rename them in the Properties window for each sheet if you want. This way you can write your code like below instead, regardless of what you name the sheets.
请参考每个表单的代码名称。它们默认设置为Sheet1，Sheet2等，但您可以根据需要在“属性”窗口中为每个工作表重命名它们。这样，无论您为工作表命名，都可以像下面那样编写代码。
With Sheet1
Row1Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
Row1Crnt = 2
With Sheet2
Row2Last = .Cells(Rows.Count, "B").End(xlUp).Row
End With
etc...
#4
0
I wanted to share my experience battling this problem. Here is the mistake I committed:
我想分享我与这个问题作斗争的经验。这是我犯的错误：
Dim DailyWSNameNew As String
lastrow = Sheets("DailyWSNameNew").Range("A65536").End(xlUp).Row + 1 -- This is wrong as I included a placeholder worksheet name in quotes
Correction:
更正：
lastrow = Sheets(DailyWSNameNew).Range("A65536").End(xlUp).Row + 1
This solved it.
这解决了它。
#5
0
I encountered this error earlier today but could not use any solution above, I did however eventually managed to solve it myself.
我今天早些时候遇到了这个错误但是无法使用上面的任何解决方案，但我最终设法自己解决了这个问题。
My situation was that I had a list contained in column A. For each cell with a value I stored the value in a variable, created a new sheet and named the sheet according to the value stored in the variable.
我的情况是我在列A中有一个列表。对于每个具有值的单元格，我将值存储在一个变量中，创建一个新的工作表并根据存储在变量中的值命名工作表。
A bit later in the code I tried to select the newly created sheet by using the code:
稍后在代码中我尝试使用代码选择新创建的工作表：
Sheets(ValueVariable).Select
I encountered the "Subscript out of range" error and I couldn't figure out why. I've used similar code before with success. I did however solve it by casting the variable as a string. Declaring the variable as a string did not seem to work for me.
我遇到了“下标超出范围”错误，我无法弄清楚原因。我成功之前使用过类似的代码。然而，我确实通过将变量转换为字符串来解决它。将变量声明为字符串似乎对我不起作用。
So, if anyone else encounter this error and want something to try, perhaps this will work for you:
所以，如果其他人遇到这个错误并想要尝试一些东西，也许这对你有用：
Sheets(Cstr(ValueVariable)).Select