我有这个 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