VBA超出范围错误 [英] Out of range error in VBA

问题描述

我想写宏来将主数据文件中的数据拆分成不同的表格。我写了下面的代码，但它显示超出范围错误。你可以帮忙吗？



Sub SplitandFilterSheet（）

'第1步 - 复​​制表

'第2步 - 按部门过滤并删除不适用的行

'第3步 - 循环直到列表末尾



Dim Splitcode As Range

表格（大师）。选择

设置Splitcode =范围（SplitCode）



每个单元格在Splitcode

表格（大师）。复制之后：=工作表（Sheets.Count）

ActiveSheet.Name = cell.Value



使用ActiveWorkbook.Sheets（cell.Value）.Range（MasterData）

.AutoFilter Field：= 5，Criterial：=<> &安培; cell.Value，运算符：= xlFilterValues

。偏移（1,0）.SpecialCells（xlCellTypeVisible）.EntireRow.Delete

结束



ActiveSheet.aitofilter.ShowAllData

下一个单元格

结束次级



< b>我尝试了什么：



我尝试了很多东西，但它没有用。有人请求帮助吗？

解决方案

我们无能为力 - 这需要你的数据和你的代码运行来解决问题所在，我们不知道可以访问任何一个。



所以，这取决于你。

幸运的是，你有一个工具可供你使用将帮助您了解正在发生的事情：调试器。 MS Excel 2013：VBA调试简介 [ ^ ]应该为您提供ghet开始所需的信息。



在函数的第一行放置断点，并通过调试器运行代码。然后查看您的代码，并查看您的数据并找出手动应该发生的事情。然后单步执行每一行检查您预期发生的情况正是如此。如果不是，那就是当你遇到问题时，你可以回溯（或者再次运行并仔细观察）以找出原因。


对不起，但我们不能为你做到这一点 - 时间让你学习一门新的（非常非常有用的）技能：调试！

引用：

我尝试了很多东西，但它不起作用。有人可以帮忙吗？

你的代码没有你想象的那样，或者你不明白为什么！



有一个几乎通用的解决方案：逐步在调试器上运行代码，检查变量。

调试器在这里向您展示您的代码正在做什么，您的任务是与它应该做什么进行比较做。

调试器中没有魔法，它不知道你的代码应该做什么，它没有找到错误，只是通过向你展示发生了什么来帮助你。当代码没有达到预期的效果时，你就接近了一个错误。

要查看你的代码在做什么：只需设置断点并查看代码是否正常运行，调试器允许你执行第1行第1行，并在执行时检查变量。

调试器 - 维基百科，免费的百科全书 [ ^ ]

在Excel VBA中进行调试 - 轻松的Excel宏 [ ^ ]

MS Excel 2013：VBA调试简介 [ ^ ]

如何调试Excel VBA - YouTube [ ^ ]



调试器在这里只显示你的代码正在做什么，你的任务是与它应该做什么进行比较。

migth是下标的几个原因超出范围错误消息。请参阅：下标范围（错误9）| Microsoft Docs [ ^ ]



第一眼看，你正试图改变<每个循环code>，这是上述错误的主要原因。


此外，我还是避免使用由于性能原因，选择/激活方法。请参阅：如何避免在Excel VBA中使用Select [< a href =https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba\"target =_ blanktitle =New Window> ^ ]

i want to write macro for splitting the data from master data file into different sheets. I have written below codes but its showing out of range error. can you please help?

Sub SplitandFilterSheet()
'step 1 - Copy sheet
'Step 2 - Filter by Department and delete rows not applicable
'step 3 - Loop until the end of the list

Dim Splitcode As Range
Sheets("Master").Select
Set Splitcode = Range("SplitCode")

For Each cell In Splitcode
Sheets("Master").Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value

With ActiveWorkbook.Sheets(cell.Value).Range("MasterData")
.AutoFilter Field:=5, Criterial:="<>" & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

ActiveSheet.aitofilter.ShowAllData
Next cell
End Sub

What I have tried:

I tried many things but its not working. can someone pleas help?

解决方案

We can't help much - this requires your data and your code running to work out what the problem might be, and we don't have access to either.

So, it's going to be up to you.
Fortunately, you have a tool available to you which will help you find out what is going on: the debugger. MS Excel 2013: VBA Debugging Introduction[^] should give you the info you need to ghet started.

Put a breakpoint on the first line in the function, and run your code through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!

Quote:

I tried many things but its not working. can someone pleas help?

Your code do not behave the way you expect, or you don't understand why !

There is an almost universal solution: Run your code on debugger step by step, inspect variables.
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't know what your code is supposed to do, it don't find bugs, it just help you to by showing you what is going on. When the code don't do what is expected, you are close to a bug.
To see what your code is doing: Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.
Debugger - Wikipedia, the free encyclopedia[^]
Debugging in Excel VBA - EASY Excel Macros[^]
MS Excel 2013: VBA Debugging Introduction[^]
How to debug Excel VBA - YouTube[^]

The debugger is here to only show you what your code is doing and your task is to compare with what it should do.

There migth be few reason of Subscript out of range error message. See: Subscript out of range (Error 9) | Microsoft Docs[^]

On the first look, you're trying to change collection in for each loop, which is the main reason of above error.

In addition, i'd avoid of using Select/Activate method due to performance. See: How to avoid using Select in Excel VBA[^].

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