时间戳

首页 > 解决方案 > 代码在执行中途停止正常工作

excel - 代码在执行中途停止正常工作

问题描述

我正在尝试从一个电子表格中收集有关球员统计数据的数据，对该数据执行计算，然后如果名称匹配，则将该数据粘贴到另一张表格的单元格中。

它有点棘手，因为带有统计数据的工作表的记录少于我的其他工作表，并且名称是缩短版本而不是全名。

我一直在使用 Range.Find 函数来检查统计表上的球员姓名是否在第一张表的姓名单元格内，如果是，请粘贴公式。

到目前为止，我的方法是： 1. 搜索长列表以查看长名称集中是否存在统计表名称 2. 如果存在，则检查长列表中的名称是否与当前行对应单元格与统计表中的名称相同，如果不是，则移至长列表中的下一个单元格并再次检查。3. 重复直到我找到与统计表单元格名称匹配的单元格位置，然后在与长列表中的名称位于同一行的单元格中填写公式。

我的代码是（它开始说所有名称都不在列表中，即使它们是）：

'
' PlayerImpact Macro
'

'
Dim rng As Range, cell As Range, PSrng As Range, player As Range, namerng As Range, FDaddress As Range, playercheck As Range
'Dim namek As String
Dim i As Long, j As Long, k As Long
Dim a As Single, b As Single, c As Single, d As Single, tot As Single
Set rng = Sheet1.Range("P2:P542")
Set namerng = Sheet1.Range("A2:A542")
Set PSrng = Sheet6.Range("A2:S390")
k = 1
i = 2
j = 2
'For j = 1 To rng.Rows.Count
Do While j < rng.Rows.Count
    Debug.Print " "
    Debug.Print "i: " & " " & i
    Debug.Print "j: " & " " & j
    Debug.Print "k: " & " " & k
    Debug.Print "Filtered Data Player:" & " " & namerng(k)
    Debug.Print "Stats Player:" & " " & PSrng.Cells(i, 3).Value
    Set cell = rng(j)
    Set player = namerng.Find(PSrng.Cells(i, 3).Value, LookIn:=xlValues, Lookat:=xlPart)
    If Not player Is Nothing Then
            Debug.Print "namerange loop: " & " " & player.Value
            Debug.Print "namerange k in loop: " & " " & namerng(k)
            Debug.Print "Stats Player in loop:" & " " & PSrng.Cells(i, 3).Value
            Set playercheck = namerng(k).Find(PSrng.Cells(i, 3).Value, LookIn:=xlValues, Lookat:=xlPart)
            If Not playercheck Is Nothing Then
                j = k
                'cell.Formula = "=('Player Stats Value'!G" & (i + 1) & "-'Player Stats Value'!$G$2)/'Player Stats Value'!$G$2+('Player Stats Value'!I" & (i + 1) & "-'Player Stats Value'!$I$2)/'Player Stats Value'!$I$2+('Player Stats Value'!J" & (i + 1) & "-'Player Stats Value'!$J$2)/(2*'Player Stats Value'!$J$2)+('Player Stats Value'!K" & (i + 1) & "-'Player Stats Value'!$K$2)/(2*'Player Stats Value'!$K$2)+('Player Stats Value'!Q" & (i + 1) & "-'Player Stats Value'!$Q$2)"
                i = i + 1
                k = k + 1
                j = j + 1
                Debug.Print "In List, Player Check Match: " & " " & player.Value
                Debug.Print "Playercheck Value: " & " " & playercheck.Value
            Else
                Debug.Print "In List, No match Player Check:" & " " & player.Value
                k = k + 1
                If k > namerng.Rows.Count Then
                    k = 1
                End If
            End If
    Else
      Debug.Print "Not in List: " & " " & PSrng.Cells(i, 3).Value
      i = i + 1
      'cell.Value = 0
      j = j + 1
      'k = k + 1
    End If
Loop

这两个电子表格可以在这里看到

开始出错时的输出示例：


i:  13
j:  14
k:  14
Filtered Data Player: Ademola Lookman
Stats Player: Adrián
namerange loop:  Adrián San Miguel del Castillo
namerange k in loop:  Ademola Lookman
Stats Player in loop: Adrián
In List, No match Player Check: Adrián San Miguel del Castillo

i:  13
j:  14
k:  15
Filtered Data Player: Adrian Mariappa
Stats Player: Adrián
namerange loop:  Adrián San Miguel del Castillo
namerange k in loop:  Adrian Mariappa
Stats Player in loop: Adrián
In List, No match Player Check: Adrián San Miguel del Castillo

i:  13
j:  14
k:  16
Filtered Data Player: Adrián San Miguel del Castillo
Stats Player: Adrián
namerange loop:  Adrián San Miguel del Castillo
namerange k in loop:  Adrián San Miguel del Castillo
Stats Player in loop: Adrián
In List, Player Check Match:  Adrián San Miguel del Castillo
Playercheck Value:  Adrián San Miguel del Castillo

i:  14
j:  17
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Adrian Mariappa
Not in List:  Adrian Mariappa

i:  15
j:  18
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Ahmed Elmohamady
Not in List:  Ahmed Elmohamady

i:  16
j:  19
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Ainsley Maitland-Niles
Not in List:  Ainsley Maitland-Niles

i:  17
j:  20
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Alex Iwobi
Not in List:  Alex Iwobi

标签： excelvba

解决方案

根据我对您的问题的了解，此代码应该可以满足您的所有需求：

Sub PlayerImpact()

Dim rng As Range, namerng As Range, PSrng As Range
Dim lngCol As Long

Set rng = Sheet1.Range("P2:P542")
Set namerng = Sheet1.Range("A2:A542")
Set PSrng = Sheet6.Range("C3:C390")

For j = 1 To namerng.Rows.Count

    If Not PSrng.Find(namerng(j).Value2, LookIn:=xlValues, Lookat:=xlPart) Is Nothing Then
        lngRow = PSrng.Find(namerng(j).Value2, LookIn:=xlValues, Lookat:=xlPart).Row

        rng(j).Formula = "=('Player Stats Value'!G" & lngRow & "-'Player Stats Value'!$G$2)/" & _
        "'Player Stats Value'!$G$2+('Player Stats Value'!I" & lngRow & "-'Player Stats Value'!$I$2) /" & _
        "'Player Stats Value'!$I$2+('Player Stats Value'!J" & lngRow & "-'Player Stats Value'!$J$2)/" & _
        "(2*'Player Stats Value'!$J$2)+('Player Stats Value'!K" & lngRow & "-'Player Stats Value'!$K$2)/" & _
        "(2*'Player Stats Value'!$K$2)+('Player Stats Value'!Q" & lngRow & "-'Player Stats Value'!$Q$2)"
    Else
        rng(j).Value2 = 0 'alternative: rng(j).Value2 = ""
    End If

Next

End Sub

推荐阅读