运行时错误9，下标超出范围

本文介绍了运行时错误9，下标超出范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我发现了一个旧的excel宏（从2005年左右开始）但我无法让它运行。 代码的前提是在名为top的工作表上使用数据库，在另一个名为bot的工作表中删除匹配的项目，并将剩余的内容组合成称为新的第3页 这些数据库基于工作中的机器生成的输出，因此不是传统的数据库风格 结束Hi, I've found an old excel macro (from around 2005) but I can't get it to run.The premise of the code takes a database on a sheet called "top" and another one called "bot" and strips out the matching items, and combines the leftovers into a 3rd sheet called "new"These databases are based on an output generated from a machine at work, so aren't in a traditional database styleIt falls over ontemp = sArray(1)Sub MergeDat() Dim countTop As Long Dim countBot As Long Dim alg As String Dim sArray As Variant Dim temp As String Dim temp2 As String Dim count As Long Dim count2 As Long Dim count3 As Long Dim tempcount As Long Dim this As Boolean countTop = 1 Do While Not Sheets("Top").Cells(countTop, 2).Value = "OCVDatabase" If Sheets("Top").Cells(countTop, 1).Value = "USER" Then alg = Sheets("Top").Cells(countTop, 2).Value 'Search for this algorithm in the bottom side countBot = 1 Do While (Not Sheets("Bottom").Cells(countBot, 2).Value = alg) And (Not Sheets("Bottom").Cells(countBot, 2).Value = "OCVDatabase") countBot = countBot + 1 Loop If Sheets("Bottom").Cells(countBot, 2).Value = alg Then 'Clear the data from the bottom sheet if it already exists in the top one. tempcount = countBot Do While Not Sheets("Bottom").Cells(tempcount, 1).Value = "" tempcount = tempcount + 1 Loop Sheets("Bottom").Select Range("A" + CStr(countBot) + ":W" + CStr(tempcount)).Select Selection.Clear 'Clear the OCV data countBot = 1 Do While Not Sheets("Bottom").Cells(countBot, 2).Value = "OCVDatabase" countBot = countBot + 1 Loop countBot = countBot + 2 Do While Not Sheets("Bottom").Cells(countBot, 2).Value = "Classifiers" sArray = Split(Sheets("Bottom").Cells(countBot, 2).Value, "[") temp = sArray(1) sArray = Split(temp, "]") temp = sArray(0) sArray = Split(temp, "ocv") temp2 = sArray(0) If temp2 = alg Then 'erase this line Sheets("Bottom").Rows(countBot).Select Selection.Clear 'Seek out the data from the above database count = 1 Do While Not Sheets("Bottom").Cells(count, 2).Value = "OCVDatabase" If Sheets("Bottom").Cells(count, 1).Value = "DEVICE" And Sheets("Bottom").Cells(count, 2).Value = temp Then 'erase it Sheets("Bottom").Select Range("A" + CStr(count) + ":W" + CStr(count + 26)).Select Selection.Clear End If count = count + 1 Loop End If countBot = countBot + 1 Loop End If End If countTop = countTop + 1 Loop' Now, compile the new database in the sheet "New," starting with the top of the top database countTop = countTop - 1 Sheets("Top").Select Range("A1:W" + CStr(countTop)).Select Selection.Copy Sheets("New").Select Range("A1").Select ActiveSheet.Paste'Delete all unnecessary entries count2 = 1 Do While count2 < countTop If Sheets("New").Cells(count2, 1).Value = "#" Then If Sheets("New").Cells(count2 - 1, 1).Value = "" Then 'delete the section count3 = count2 Do While Not Sheets("New").Cells(count3, 1).Value = "" count3 = count3 + 1 Loop Rows(CStr(count2) + ":" + CStr(count3)).Select Selection.Delete Shift:=xlUp countTop = countTop - (count3 - count2) - 1 End If End If count2 = count2 + 1 Loop countTop = countTop + 1 countBot = 7 this = False Do While this = False 'Find the first lot of writing Do While Sheets("Bottom").Cells(countBot, 2).Value = "" countBot = countBot + 1 Loop 'Make sure it's a useful part Do While Sheets("Bottom").Cells(countBot, 1).Value = "#" Do While Not Sheets("Bottom").Cells(countBot, 2).Value = "" countBot = countBot + 1 Loop Do While Sheets("Bottom").Cells(countBot, 1).Value = "" countBot = countBot + 1 Loop Loop count = countBot 'Find the end of the writing Do While Not Sheets("Bottom").Cells(countBot, 2).Value = "" If Sheets("Bottom").Cells(countBot, 2).Value = "Classifiers" Then this = True End If countBot = countBot + 1 Loop countBot = countBot 'Paste it in Sheets("Bottom").Select Range("A" + CStr(count) + ":W" + CStr(countBot)).Select Selection.Copy Sheets("New").Select Range("A" + CStr(countTop)).Select ActiveSheet.Paste Do While Not Sheets("New").Cells(countTop, 1).Value = "" countTop = countTop + 1 Loop countTop = countTop + 1 Loop countBot = countTop countTop = 1 Do While Not Sheets("Top").Cells(countTop, 2).Value = "OCVDatabase" countTop = countTop + 1 Loop count = countTop + 2 Do While Not Sheets("Top").Cells(countTop, 2).Value = "Classifiers" countTop = countTop + 1 Loop Sheets("Top").Select Range("A" + CStr(count) + ":E" + CStr(countTop)).Select Selection.Copy Sheets("New").Select Range("A" + CStr(countBot - 2)).Select ActiveSheet.Paste BeepEnd Sub 我尝试过： 我已经尝试到处寻找解决方案，但我几乎不了解VBA，所以我不确定即使我修复了这个特定错误，它也可能会停止工作line！What I have tried:I've tried searching everywhere for a solution, but I have virtually no understanding of VBA, so I'm not sure even if I get this particular error fixed, that it might stop working on the next line!推荐答案引用：尝试到处寻找解决方案，但我几乎没有理解VBA，所以我不确定即使我修复了这个特定的错误，它可能会停止在下一行工作！'ve tried searching everywhere for a solution, but I have virtually no understanding of VBA, so I'm not sure even if I get this particular error fixed, that it might stop working on the next line! 还有问题。我们也不能 - 我们甚至无法测试它，因为我们无法访问您的数据。 想一想：你会尝试解决吗？如果你不知道如何使用扳手，你的车上会刹车吗？如果你这样做，你会很乐意在城镇周围驾驶你的妻子和孩子吗？不 - 因为你知道你需要有人知道他在做什么，而现在不是你！你需要先用简单的东西训练自己：正确更换车轮是刹车工作的必备先决条件，这意味着要知道如何正确地抬起并支撑汽车，这样你就不会被压死！ /> 对不起，但你需要有VBA专业知识的人为你编写代码 - 只需查找旧代码并希望你能抨击它并得到它它在不理解它所写的语言的情况下工作并不是成功的秘诀！ 你有三个选择： 1）学习VBA并自己动手。 3）找到一些你已经理解的方法。And there is the problem. We can't either - and we can't even test it because we don't have access to your data.Think about it: would you try to fix the brakes on your car if you don't know how to use a spanner? And would you be happy to drive your wife and children around town afterwards if you did? No - because you know you need someone who knows what he is doing, and that isn't you at the moment! You need to train yourself on the simple stuff first: changing a wheel correctly is an essential prerequisite for brake work, and that means knowing how to correctly jack up and support a car so you don't get crushed to death doing it!I'm sorry, but you need someone with VBA expertise to write code for you - just finding old code and hoping that you can "bash it about a bit" and get it working without understanding the language it is written in just isn't a recipe for success!You have three options:1) Learn VBA and do it yourself.2) Pay someone with the right skills and expertise to do it for you.3) Find some way to do it that you do understand already. Quote：它落在了It falls over ontemp = sArray(1)然后数组可能只包含一个项目。看看 sArray 的设置（上一行）：Then the array contains probably only a single item. Have a look where sArray is set (the previous line):sArray = Split(Sheets("Bottom").Cells(countBot, 2).Value, "[")当字符串被发生时splitted不包含'['字符。 但是如解决方案1中所述： 我们没有您的数据以便我们不能再帮助了。 我所能猜到的是你的数据不是预期的格式。That happens when the string to be splitted does not contain a '[' character.But as explained in solution 1:We do not have your data so that we can't help further.All I can guess is that your data are not in the expected format. 这篇关于运行时错误9，下标超出范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！