下标超出范围重命名页面后出错 [英] Subscript out of range Error after renaming sheets

问题描述

我已经完成了一个小项目，其中包括5个excel表，代码正常工作，我也得到了精确的结果，但是如果我从sheet1重命名为其他名称，我得到了下标超出范围错误。

这是什么原因，需要做些什么来克服这个问题。请帮助。

以下是代码

  Public Sub amount_final ）
 
 Dim Row1Crnt As Long 
 Dim Row2Crnt As Long 
 
 
带表格（sheet4）
 Row1Last = .Cells（Rows结束（xlUp）.Row 
结束
 
 Row1Crnt = 2 
带表格（sheet3）
 Row2Last = .Cells （Rows.Count，B）。End（xlUp）.Row 
结束
  

解决方案

代码本身没有错。如果Excel无法找到特定的表格，那么您将获得下标超出范围错误。例如，如果您将表格Sheet3重命名为SheetXYZ，则Excel将无法找到。

避免这些错误的唯一方法是使用表格的CODENAME。请参阅快照

这里我们有一张名称为重命名前的样本名称的表格

所以考虑这个代码

 表格（重命名前的样本名称）范围（A1）Value =Blah Blah
  

相同的代码可以写成

  Sheet2.Range（A1）。Value =Blah Blah
  

你重命名页面的次数，上面的代码将始终有效：）

HTH

Sid

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.

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

解决方案

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.

The only way to avoid these kind of errors is to use CODENAME of the sheets. See Snapshot

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

Sid

这篇关于下标超出范围重命名页面后出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！