Excel VBA：编译器错误 [英] Excel VBA: Compiler Errors

问题描述

所以昨天我张贴了我的第一个问题，它下降像一吨砖。但是，我已经选择了自己，打扫自己，希望这个问题会更可以接受...： - ）

So yesterday I posted my first SO question, and it went down like a ton of bricks. However I've picked myself up, dusted myself off, and hopefully this question will be more acceptable... :-)

我试图删除数据重复的健康问卷清单我必须监控，但我困扰的棘手的是找到一个重复的一列，然后检查同一行，3个相邻列的数据也是重复。存储搜索的'重复行'是有点被扔了我。

I am trying to remove data duplicates from a list of Health Questionnaires I have to monitor, but the tricky bit I was struggling with was finding a duplicate in one column, AND then checking that the data on the same row, for the 3 adjacent columns were also duplicates. Storing the searched for 'duplicated row' was the bit that was throwing me off.

这里有一些代码我从其他类似功能的脚本拼凑在一起。我现在在调试模式，并不断得到错误抛出...我没有太多的VBA经验，所以我用尽了选项。

Here's some code I've cobbled together from other similarly-functioning scripts. I'm now in debug mode and keep getting errors thrown up... I don't have much experience of VBA, so i'm running out of options.

我目前得到类型不匹配错误与变量 g ，还有 firstAddress 。为什么会导致这些问题？

I'm currently getting type mismatch errors with the variable g, and also firstAddress. Why are these causing problems???

我可以调用 firstAddress.Row ？

以下是代码段：

g = .Find(Range("G" & i).Text, LookIn:=xlValues)
            If Not g Is Nothing Then
                firstAddress = g.Address
                dupRow = firstAddress.Row

下面是整个代码。任何帮助将非常感谢！

And here's the whole code below. Any help would be much appreciated!

Sub FindCpy()
Dim lw As Long
Dim i As Integer
Dim sh As Worksheet
Dim dupRow As Integer
Dim g As Integer
Dim firstAddress As Integer


'Used for the new worksheet we are pasting into
Dim objNewSheet As Worksheet
Dim rngNextAvailbleRow As Range

'Used to narrow down the logical operators for duplicates
Dim rngFirst As Range

'Set the ranges
rngFirst = Range("G" & 1, "G" & lw)

Set sh = Sheets("Completed")
lw = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lw 'Find duplicates from the list.
    If Application.CountIf(Range("A" & i & ":A" & lw), Range("A" & i).Text) = "Complete" Then

    'if COMPLETE, check the rest of the sheet for any 'in progress' duplicates...
    With Worksheets("Still In Progress").rngFirst
        g = .Find(Range("G" & i).Text, LookIn:=xlValues)
        If Not g Is Nothing Then
            firstAddress = g.Address
            dupRow = firstAddress.Row
            If Range("H" & dupRow).Text = Range("H" & i).Text _
            And Range("I" & dupRow).Text = Range("I" & i).Text _
            And Range("J" & dupRow).Text = Range("J" & i).Text Then

        'select the entire row
        Range.EntireRow.Select

        'copy the selection
        Selection.Cut

        'Now identify and select the new sheet to paste into
        Set objNewSheet = ThisWorkbook.Worksheets("Completed")
        objNewSheet.Select

        'Looking at your initial question, I believe you are trying to find the next     available row
        Set rngNextAvailbleRow = objNewSheet.Range("A1:A" & objNewSheet.Cells(Rows.Count, "A").End(xlUp).Row)

        Range("A" & rngNextAvailbleRow.Rows.Count + 1).Select
        ActiveSheet.Paste

        'delete the initial row
        rngCell.EntireRow.Delete

        Set g = .FindNext(g)
            Loop While Not g Is Nothing And g.Address <> firstAddress
        End If
    End With
Next i
End Sub

推荐答案

我仔细阅读了你的代码。有一些问题。其中一些我认为我能够解决 - 有一个我猜想你打算做什么，但对于其中一个我只是标记它;你需要解释你正在尝试做什么，因为你正在删除一个范围，你从未定义...

I went through your code carefully. There were a number of problems. Some of these I think I was able to fix - there was one where I guessed what you intended to do, but for one of them I just marked it; you need to explain what you were trying to do, as you are deleting a range that you never defined...

第一个问题是行：

If Application.CountIf(Range("A" & i & ":A" & lw), Range("A" & i).Text) = "Complete" Then

CountIf 函数返回一个数字;您正在将此数字与字符串完成进行比较。我不认为你可以永远不会超过这一行，所以其余的代码（无论正确与否）将不会执行。不完全清楚你在这行中要做什么，因为我不知道什么时候一行会被标记为完成 - 但假设你有兴趣执行剩余的代码，如果 A&

The CountIf function returns a number; you are comparing this number with the string "Complete". I don't think you can ever get past this line, so the rest of the code (whether correct or not) will not execute. Not entirely clear what you are trying to do in this line, as I'm not sure when a line will be marked "Complete" - but assuming that you are interested in executing the rest of the code if the cell in A & i has the string "Complete" in it, then you probably want to do

If Range("A" & i).Text = "Complete" Then

c $ c> If - Then ，使用和循环以 End 结尾。我试图补救这一点 - 确保我做到了正确。注意，使用适当的缩进真的有助于找到这样的问题。空格键是您的朋友...

There were a number of If - Then, With, and Loop structures that were not properly terminated with a matching End. I have tried to remedy this - make sure I did it right. Note that using proper indentation really helps to find problems like this. The space bar is your friend...

由于 Find 方法返回一个对象，正确的使用方法函数是

Since the Find method returns an object, the correct way to use the function is

Set g = .Find(Range("G" & i).Text, LookIn:=xlValues)

除此之外 - 使用 Option Explicit 代码的顶部，并定义具有最严格（正确）类型的变量，您可以。当我这样做，我发现错误，我不能正确 - 与 rngCell 变量既没有声明，也没有设置...它显示它是多么有帮助。对于捕捉拼写错误也很好 - VBA会很高兴地让你写下像

Apart from that - use Option Explicit at the top of your code, and define variables with the most restrictive (correct) type that you can. When I did this I found the error I could not correct - with the rngCell variable that was neither declared, nor ever set... It shows just how helpful it can be. Also good for catching typos - VBA will happily let you write things like

myVar = 1
MsgBox myVra + 1

myVar = 1 MsgBox myVra + 1

该消息将是 1 ，而不是 2 ，因为错字...事实显式应该是一个选项是VBA团队做出的许多莫名其妙的设计决策之一。

The message will be 1, not 2, because of the typo... The fact that Explicit should even be an option is one of the many inexplicable design decisions made by the VBA team.

这里是你的代码大部分的错误修复。至少像这样它会编译 - 但你必须弄清楚如何处理剩余的错误（我不能确定我猜想你想要做的标记为完成的单元格）。

Here is your code "with most of the errors fixed". At least like this it will compile - but you must figure out what to do with the remaining error (and I can't be sure I guessed right about what you wanted to do with the cell marked "Complete").

注释欢迎。

Option Explicit

Sub FindCpy()
Dim lw As Long
Dim i As Integer
Dim sh As Worksheet
Dim dupRow As Integer
Dim g As Range
Dim firstAddress As Range

'Used for the new worksheet we are pasting into
Dim objNewSheet As Worksheet
Dim rngNextAvailbleRow As Range

'Used to narrow down the logical operators for duplicates
Dim rngFirst As Range

'Set the ranges
rngFirst = Range("G" & 1, "G" & lw)

Set sh = Sheets("Completed")
lw = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lw 'Find duplicates from the list.
'  If Application.CountIf(Range("A" & i & ":A" & lw), Range("A" & i).Text) = "Complete" Then
   If Range("A" & i).Text = "Complete" Then
   'if COMPLETE, check the rest of the sheet for any 'in progress' duplicates...
    With Worksheets("Still In Progress").rngFirst
      Set g = .Find(Range("G" & i).Text, LookIn:=xlValues)
        If Not g Is Nothing Then
          firstAddress = g.Address
          dupRow = firstAddress.Row
          If Range("H" & dupRow).Text = Range("H" & i).Text _
            And Range("I" & dupRow).Text = Range("I" & i).Text _
            And Range("J" & dupRow).Text = Range("J" & i).Text Then

            'select the entire row
            g.EntireRow.Select

            'copy the selection
            Selection.Cut

            'Now identify and select the new sheet to paste into
            Set objNewSheet = ThisWorkbook.Worksheets("Completed")
            objNewSheet.Select

            'Looking at your initial question, I believe you are trying to find the next     available row
            Set rngNextAvailbleRow = objNewSheet.Range("A1:A" & objNewSheet.Cells(Rows.Count, "A").End(xlUp).Row)

            Range("A" & rngNextAvailbleRow.Rows.Count + 1).Select
            ActiveSheet.Paste

            'delete the initial row
            rngCell.EntireRow.Delete  ' <<<<<< the variable rngCell was never defined. Cannot guess what you wanted to do here!

            Do
              Set g = .FindNext(g)
              Loop While Not g Is Nothing And g.Address <> firstAddress

          End If ' entire row matched
        End If   ' Not g Is Nothing
      End With   ' With Worksheets("Still in Progress")
    End If       ' CountIf = "Complete"

  Next i

End Sub

另一个方便的技巧：当您使用 Range（A& rngNextAvailbleRow.Rows.Count + 1）粘贴到下一个可用的行。选择，我通常很容易做这样的事情：

Another handy trick: when you "paste in the next available row" as you are doing with Range("A" & rngNextAvailbleRow.Rows.Count + 1).Select, I usually find it handy to do something like this instead:

Dim destination As Range
Set destination = Worksheets("Sheetname").Range("A1")

destination.Select
ActiveSheet.Paste
Set destination = destination.Offset(1,0)

这样， destination 下一个地方我可以粘贴。我觉得它有帮助和清洁。

This way, destination is always pointing to the "next place where I can paste". I find it helpful and cleaner.

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