运行时错误9，下标超出范围 [英] Run time error 9, subscript out of range
问题描述
我发现了一个旧的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 style
It falls over on
temp = 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
Beep
End 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并自己动手。< b）支付具有适当技能和专业知识的人为你做这件事。
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.
它落在了
It falls over on
temp = 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，下标超出范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
