Excel VBA Vlookup 运行时错误 1004 [英] Excel VBA Vlookup Runtime Error 1004

问题描述

我正在尝试创建一个函数，该函数将运行一个循环来测试一个组织(var 'org')是否有一个实际已经开始的活动，因此 'If (result <= Now()'.我在带有CountIf"的电子表格中找到了未指定数量的活动，并作为总计"提供给模块.

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'.

在电子表格中，当需要有一个有效活动的单元格发现另一个单元格中随机猜测的活动无效时，它会转到 VBA 函数，为该函数提供组织 ID 和活动总数在那个组织之下.

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.

我的代码:

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

电子表格结构:

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

在 VBA 编辑器中调试期间，runtime error 1004 出现，在电子表格中执行时，该函数似乎什么也不做，单元格在相当快地刷新单元格之前采用最后一个有效值.我该如何解决这个问题?

During debugging in VBA editor the runtime error 1004 crops 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?

推荐答案

所以对于那些以后可能会遇到这个问题的人，这是我的工作代码:

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

基本上On Error Resume Next"解决了这个问题.然后我添加了 If IsNull 检查结果.

Basically the 'On Error Resume Next' fixed the issue. Then I added the If IsNull check for the result.

我还稍微改进了代码，它现在可以随机选择任何一个有效的广告系列.之前它会简单地选择它找到的第一个有效活动.

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.

这篇关于Excel VBA Vlookup 运行时错误 1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！