编译错误:Excel 宏中的语法错误

编译错误:Excel 宏中的语法错误

我有这个 VBA 代码,当我尝试运行它时,它给出“编译错误:语法错误”,如图所示。我不懂 VBA,我应该怎么做才能让这个代码工作?谢谢。

 Sub MostCommonPairAndTriplet()
Dim rng As Range
Dim c As Range
Dim strPair As String
Dim strTriplet As String
Dim wsResult As Worksheet
Dim lRow As Long
Dim lRow2 As Long
Dim i As Integer
Dim j As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:F"))

If Not rng Is Nothing Then

'Get the result worksheet
On Error Resume Next
Set wsResult = ActiveWorkbook.Worksheets("Results")
If wsResult Is Nothing Then
Set wsResult = ActiveWorkbook.Worksheets.Add
wsResult.Name = "Results"
Else
wsResult.UsedRange.Delete
End If
'column labels
With wsResult
.Range("B1").Value = "Value1"
.Range("C1").Value = "Value2"
.Range("D1").Value = "Count"
.Range("F1").Value = "Value1"
.Range("G1").Value = "Value2"
.Range("H1").Value = "Value3"
.Range("I1").Value = "Count"
End With
On Error GoTo 0

'Find Pairs
lRow = 2
For Each c In rng
If c.Column <= 5 Then
For i = 1 To 6 - c.Column
strPair = c.Value & "_" & c.Offset(0, i).Value

On Error Resume Next
lRow2 = Application.WorksheetFunction.Match(strPair,
wsResult.Range("A:A"), False)
If Err.Number > 0 Then
wsResult.Range("A" & lRow).Value = strPair
wsResult.Range("B" & lRow).Value = c.Value
wsResult.Range("C" & lRow).Value = c.Offset(0,
i).Value
wsResult.Range("D" & lRow).Value = 1
lRow = lRow + 1
Else
wsResult.Range("D" & lRow2).Value =
wsResult.Range("D" & lRow2).Value 1
End If
On Error GoTo 0
Next i
End If
Next c

'Find Triplets
lRow = 2
For Each c In rng
If c.Column <= 5 Then
For i = 1 To 6 - c.Column
For j = 1 To 6 - c.Offset(0, i).Column
strTriplet = c.Value & "_" & c.Offset(0, i).Value &
"_" & c.Offset(0, i + j).Value

On Error Resume Next
lRow2 =
Application.WorksheetFunction.Match(strTriplet, wsResult.Range("E:E"), False)
If Err.Number > 0 Then
wsResult.Range("E" & lRow).Value = strTriplet
wsResult.Range("F" & lRow).Value = c.Value
wsResult.Range("G" & lRow).Value = c.Offset(0,
i).Value
wsResult.Range("H" & lRow).Value = c.Offset(0, i
+ j).Value
wsResult.Range("I" & lRow).Value = 1
lRow = lRow + 1
Else
wsResult.Range("I" & lRow2).Value =
wsResult.Range("I" & lRow2).Value 1
End If
On Error GoTo 0
Next j
Next i
End If
Next c
End If

wsResult.Columns("E").Clear
wsResult.Columns("A").Delete

'Sort the pairs
With wsResult
.Columns("A:C").Sort Key1:=.Range("C2"), Order1:=xlDescending
.Columns("E:H").Sort Key1:=.Range("H2"), Order1:=xlDescending
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

在此处输入图片描述

答案1

您的代码中有许多地方存在不必要的换行符。您发布的屏幕截图显示许多行以红色突出显示;这些地方会出现语法错误,因为这些行不完整。

您要断掉的那行应该与下一行合并,以得到以下结果:

lRow2 = Application.WorksheetFunction.Match(strPair,wsResult.Range("A:A"), False)

在这种情况下,该行试图lRow2使用 Excel 的内置MATCH函数为变量分配一个值,该函数在某个范围内查找值并返回找到匹配项的行号。但是,由于您的行不完整,因此它必须使用参数来告诉它要搜索哪个值。您可以通过多种方式判断它是不完整的 - 它以红色突出显示,只有一个参数,并且它有一个左括号而没有右括号。

在 VBA 中,每个单独的指令或方法都应包含在一行中。如果为了便于阅读需要跨多行,可以使用下划线_将两行连接在一起。以下是您的代码,已修改以避免换行:

編輯:

我假设剩余的两条错误行会记录找到的某个值的数量,因此它们每次只会将特定单元格中的值加 1。运行一下,然后告诉我你得到了什么。

Sub MostCommonPairAndTriplet()
    Dim rng As Range
    Dim c As Range
    Dim strPair As String
    Dim strTriplet As String
    Dim wsResult As Worksheet
    Dim lRow As Long
    Dim lRow2 As Long
    Dim i As Integer
    Dim j As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("A:F"))

    If Not rng Is Nothing Then

    'Get the result worksheet
    On Error Resume Next
    Set wsResult = ActiveWorkbook.Worksheets("Results")
    If wsResult Is Nothing Then
    Set wsResult = ActiveWorkbook.Worksheets.Add
    wsResult.Name = "Results"
    Else
    wsResult.UsedRange.Delete
    End If
    'column labels
    With wsResult
    .Range("B1").Value = "Value1"
    .Range("C1").Value = "Value2"
    .Range("D1").Value = "Count"
    .Range("F1").Value = "Value1"
    .Range("G1").Value = "Value2"
    .Range("H1").Value = "Value3"
    .Range("I1").Value = "Count"
    End With
    On Error GoTo 0

    'Find Pairs
    lRow = 2
    For Each c In rng
        If c.Column <= 5 Then
            For i = 1 To 6 - c.Column
                strPair = c.Value & "_" & c.Offset(0, i).Value

                On Error Resume Next
                lRow2 = Application.WorksheetFunction.Match(strPair, wsResult.Range("A:A"), False)
                If Err.Number > 0 Then
                    wsResult.Range("A" & lRow).Value = strPair
                    wsResult.Range("B" & lRow).Value = c.Value
                    wsResult.Range("C" & lRow).Value = c.Offset(0, i).Value
                    wsResult.Range("D" & lRow).Value = 1
                    lRow = lRow + 1
                Else
                    wsResult.Range("D" & lRow2).Value = wsResult.Range("D" & lRow2).Value + 1
                End If
                On Error GoTo 0
            Next i
        End If
    Next c

    'Find Triplets
    lRow = 2
    For Each c In rng
        If c.Column <= 5 Then
            For i = 1 To 6 - c.Column
                For j = 1 To 6 - c.Offset(0, i).Column
                    strTriplet = c.Value & "_" & c.Offset(0, i).Value & "_" & c.Offset(0, i + j).Value

                    On Error Resume Next
                    lRow2 = Application.WorksheetFunction.Match(strTriplet, wsResult.Range("E:E"), False)
                    If Err.Number > 0 Then
                        wsResult.Range("E" & lRow).Value = strTriplet
                        wsResult.Range("F" & lRow).Value = c.Value
                        wsResult.Range("G" & lRow).Value = c.Offset(0, i).Value
                        wsResult.Range("H" & lRow).Value = c.Offset(0, i + j).Value
                        wsResult.Range("I" & lRow).Value = 1
                        lRow = lRow + 1
                    Else
                        wsResult.Range("I" & lRow2).Value = wsResult.Range("I" & lRow2).Value + 1
                    End If
                    On Error GoTo 0
                Next j
            Next i
        End If
    Next c
    End If

    wsResult.Columns("E").Clear
    wsResult.Columns("A").Delete

    'Sort the pairs
    With wsResult
    .Columns("A:C").Sort Key1:=.Range("C2"), Order1:=xlDescending
    .Columns("E:H").Sort Key1:=.Range("H2"), Order1:=xlDescending
    End With


    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

相关内容