Excel VBA Vlookup 运行时错误 1004
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20971739/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Vlookup Runtime Error 1004
提问by Edward G-Jones
I'm trying to create a function that will run a loop that tests to see whether or not an organization (var 'org') has a campaign that has actually started yet, hence 'If (result <= Now()'. There is an unspecified number of campaigns which I'm finding in the spreadsheet with 'CountIf' and is given to the module as 'total'.
我正在尝试创建一个函数，该函数将运行一个循环来测试一个组织 (var 'org') 是否有一个实际已经开始的活动，因此是 'If (result <= Now()'。有是我在带有“CountIf”的电子表格中找到的未指定数量的活动，并作为“总计”提供给模块。
In the spreadsheet when the cell which needs to have a valid campaign finds that the campaign randomly guessed in another cell isn't valid, it goes to the VBA function, giving the function both the organization ID and the total number of campaigns under that organization.
在电子表格中，当需要有一个有效活动的单元格发现另一个单元格中随机猜测的活动无效时，它会转到 VBA 函数，为该函数提供组织 ID 和该组织下的活动总数.
My codes:
我的代码：
Sub Macro()
Dim x
x = MacIDGen(111, 11)
End Sub
Function MacIDGen(org, total)
Dim iteration As Boolean, result As Range
For current = 1 To total
result = Application.WorksheetFunction.VLookup(org & " " & current, ActiveWorkbook.Sheets("Donations").Range("C:E"), 3, False)
If (result <= Now()) Then
MacIDGen = org & " " & current & " Test successful"
current = total
End If
Next current
End Function
Spreadsheet structure:
电子表格结构：
Org ID- Org Camp Count- Camp No.- Valid Camp No.
62 1 1 62 1
14 2 1 14 1
2 4 4 2 4
79 5 4 79 4
During debugging in VBA editor the runtime error 1004crops up and when executing in the spreadsheet the function seeming does nothing and the cell adopts last valid value before fairly quickly refreshing cells.
How can i fix this?
在 VBA 编辑器中调试期间runtime error 1004，在电子表格中执行时，该函数似乎什么都不做，单元格在相当快地刷新单元格之前采用最后一个有效值。我怎样才能解决这个问题？
回答by Edward G-Jones
So for those who may stumble across this later, here's my working code:
所以对于那些以后可能会偶然发现的人来说，这是我的工作代码：
Function MacIDGen2(org As Integer, total As Integer)
Dim iteration As Boolean, trueArray() As String, totalTrue As String, randArrNo As Integer, result
ReDim trueArray(total)
totalTrue = 0
For current = 0 To total - 1
On Error Resume Next
result = Application.WorksheetFunction.VLookup(org & " " & current + 1, ActiveWorkbook.Sheets("Campains").Range("C:E"), 3, False)
On Error GoTo 0
If (Not IsNull(result)) Then
If (result <= Now()) Then
trueArray(totalTrue) = current + 1
totalTrue = totalTrue + 1
End If
End If
Next current
If (totalTrue > 0) Then
randArrNo = WorksheetFunction.RandBetween(0, totalTrue - 1)
MacIDGen2 = org & " " & trueArray(randArrNo)
Else
MacIDGen2 = 0
End If
End Function
Basically the 'On Error Resume Next' fixed the issue. Then I added the If IsNull check for the result.
基本上“On Error Resume Next”解决了这个问题。然后我添加了 If IsNull 检查结果。
I've also slightly improved the code in that it now randomly selects any one of the valid campaigns. Before it would simply pick the first valid campaign it finds.
我还稍微改进了代码，因为它现在可以随机选择任何一个有效的广告系列。之前它会简单地选择它找到的第一个有效活动。
Those with a keen eye may also notice that the sheet I'm referencing in the updated version is different to the one in my original code. The original sheet was wrong and I was referencing the same sheet I was calling the module from, ending in a circular reference. This tiny little discrepancy cost me over a couple of hours of hair tearing confusion.
那些有敏锐眼光的人可能还会注意到，我在更新版本中引用的工作表与我原始代码中的工作表不同。原始工作表是错误的，我引用了我从中调用模块的同一张工作表，以循环引用结尾。这个微小的差异让我花了几个小时的头发撕裂混乱。
