我已经编写了一些VBA来对一行中各列的值求和。然而，我发现当我运行代码时，它在随机行上收到一个类型不匹配的错误，我不知道为什么。当我删除除了要添加的四列之外的所有其他列时，它突然没有这个错误了吗？
我还注意到，由于某些原因；发球选手反手"；列值，我不知道为什么。
Sub sumShotsInRally()
'Set rawData sheet as active
Dim sht1 As Worksheet
Set sht1 = Sheets("Input")
sht1.Activate
'Find the Columns to Add
Dim serverForehandColNum As Integer
serverForehandColNum = ActiveSheet.Rows(1).Find(what:="Serving player forehand", lookat:=xlWhole).Column
Dim serverBackhandColNum As Integer
serverBackhandColNum = ActiveSheet.Rows(1).Find(what:="Serving player backhand", lookat:=xlWhole).Column
Dim returnerForehandColNum As Integer
returnerForehandColNum = ActiveSheet.Rows(1).Find(what:="Returning player forehand", lookat:=xlWhole).Column
Dim returnerBackhandColNum As Integer
returnerBackhandColNum = ActiveSheet.Rows(1).Find(what:="Returning player backhand", lookat:=xlWhole).Column
'Insert two new columns for the x and y
ActiveSheet.Columns(serverForehandColNum + 1).Insert
' Add New col headings
ActiveSheet.Cells(1, serverForehandColNum + 1).Value = "Rally Count"
Dim rallyCountColNum As Integer
rallyCountColNum = ActiveSheet.Rows(1).Find(what:="Rally Count", lookat:=xlWhole).Column
'Split the cell values
'Define the range to iterate over as the used range of the found column
Dim SForehandRange As Range
Dim SBackhandRange As Range
Dim RForehandRange As Range
Dim RBackhandRange As Range
Dim rallyRange As Range
With ActiveSheet
Set SForehandRange = .Range(.Cells(2, serverForehandColNum), .Cells(.UsedRange.Rows.Count, serverForehandColNum))
Set SBackhandRange = .Range(.Cells(2, serverBackhandColNum), .Cells(.UsedRange.Rows.Count, serverBackhandColNum))
Set RForehandRange = .Range(.Cells(2, returnerForehandColNum), .Cells(.UsedRange.Rows.Count, returnerForehandColNum))
Set RBackhandRange = .Range(.Cells(2, returnerBackhandColNum), .Cells(.UsedRange.Rows.Count, returnerBackhandColNum))
Set rallyRange = .Range(.Cells(2, rallyCountColNum), .Cells(.UsedRange.Rows.Count, rallyCountColNum))
End With
Dim results()
'You redimension the results array to the number of entries in your table
ReDim results(1 To SForehandRange.Rows.Count)
'You loop over your table and sum the values from count and restocked
For i = 1 To SForehandRange.Rows.Count
rallyRange(i, 1).Value = SForehandRange(i, 1).Value + SBackhandRange(i, 1).Value + RForehandRange(i, 1).Value + RBackhandRange(i, 1).Value
'results(i) = SForehandRange(i, 1) + SBackhandRange(i, 1) + RForehandRange(i, 1) + RBackhandRange(i, 1)
Next i
'You write the array to the range count and delete the values in restocjed
'rallyRange = Application.Transpose(results)
End Sub
表格示例：
+------------------+----------+----------+-------------------+-------------------+------------------+------------+--------------------+-------------------+-------------------+-------------+------------------+------------+------------+--------------------+------------+-----------------+--------------+--------------+-----------+-----------+-----------+---------------+----------------+-----------------+--------------------+-------------------+-------------+----------------------+-----------------------+------------------------+---------------------+---------------------+----------------------+-----------------------+--------------------+---------------+-------------------+--------------+-----------+---------------------+--------------+-------------+---------------------------+-------------------------+---------------+---------------------+----------------------+-----------------------+--------------------+--------------+----------------------+-----------------------+------------------------+---------------------+---------------+---------------------+---------------------------+-------------------------+-----------------------+
| Name | Position | Duration | 1st serve outcome | 1stReturnLocation | 1stServeLocation | 1stServeXY | 2nd return outcome | 2nd return stroke | 2nd serve outcome | 2ndReturnXY | 2ndServeLocation | 2ndServeXY | Date | Final shot outcome | Game score | Opponent player | Point score | Point won by | S/R | Set score | Side | Tagged player | Tiebreak score | Tournament name | 1st return outcome | 1st return stroke | 1stReturnXY | 2nd return+1 outcome | 2nd return+1 position | 2nd return+1 situation | 2nd return+1 stroke | 2nd serve+1 outcome | 2nd serve+1 position | 2nd serve+1 situation | 2nd serve+1 stroke | 2ndReturn+1XY | 2ndReturnLocation | 2ndServe+1XY | Final hit | Final shot position | Final stroke | FinalShotXY | Returning player forehand | Serving player forehand | Type | 1st serve+1 outcome | 1st serve+1 position | 1st serve+1 situation | 1st serve+1 stroke | 1stServe+1XY | 1st return+1 outcome | 1st return+1 position | 1st return+1 situation | 1st return+1 stroke | 1stReturn+1XY | Tagged net approach | Returning player backhand | Serving player backhand | Opponent net approach |
+------------------+----------+----------+-------------------+-------------------+------------------+------------+--------------------+-------------------+-------------------+-------------+------------------+------------+------------+--------------------+------------+-----------------+--------------+--------------+-----------+-----------+-----------+---------------+----------------+-----------------+--------------------+-------------------+-------------+----------------------+-----------------------+------------------------+---------------------+---------------------+----------------------+-----------------------+--------------------+---------------+-------------------+--------------+-----------+---------------------+--------------+-------------+---------------------------+-------------------------+---------------+---------------------+----------------------+-----------------------+--------------------+--------------+----------------------+-----------------------+------------------------+---------------------+---------------+---------------------+---------------------------+-------------------------+-----------------------+
| 0-0 (1) | 329720 | 23520 | Error | Error net | Error net | 38;52 | Error | Forehand | In | 65;54 | Wide | 32;38 | 20/08/2011 | Forced error | 00:00 | Player 2 | 0-0 | Player | Serving | Set 1 | Deuce | Player 1 | 00:00 | Repton | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 15-0 (1) | 375000 | 4720 | In | Error long | Body | 60;32 | | | | | | | 20/08/2011 | Unforced error | 00:00 | Player 2 | 15-0 | Player | Serving | Set 1 | Advantage | Player 1 | 00:00 | Repton | Error | Backhand | 65;5 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 30-0 (1) | 393320 | 17440 | Error | | Error net | 44;51 | In | Forehand | In | 57;27 | Body | 42;34 | 20/08/2011 | Unforced error | 00:00 | Player 2 | 30-0 | Player | Serving | Set 1 | Deuce | Player 1 | 00:00 | Repton | | | | In | Middle baseline | Neutral | Backhand | In | Middle baseline | Neutral | Forehand | 59;28 | Middle | 61;27 | Player 1 | Middle baseline | Forehand | 39;28 | 1 | 1 | Ground stroke | | | | | | | | | | | | | | |
| 40-0 (1) | 428640 | 6360 | In | Middle | Wide | 66;36 | | | | | | | 20/08/2011 | | 00:00 | Player 2 | 40-0 | Player | Serving | Set 1 | Advantage | Player 1 | 00:00 | Repton | In | Backhand | 46;20 | | | | | | | | | | | | | | | | | | | Unforced error | Middle baseline | Neutral | Forehand | 48;29 | | | | | | | | | |
| 40-15 (1) | 450800 | 9840 | In | Middle | Wide | 34;34 | | | | | | | 20/08/2011 | Unforced error | 00:00 | Player 2 | 40-15 | Player | Serving | Set 1 | Deuce | Player 1 | 00:00 | Repton | In | Forehand | 55;28 | | | | | | | | | | | | Player 1 | Middle transition | Forehand | 69;50 | | 1 | Approach shot | In | Middle baseline | Neutral | Forehand | 57;28 | In | Middle baseline | Neutral | Backhand | 69;23 | Yes | | | |
| 40-30 (1) | 485280 | 6680 | In | Middle | Body | 60;33 | | | | | | | 20/08/2011 | | 00:00 | Player 2 | 40-30 | Player | Serving | Set 1 | Advantage | Player 1 | 00:00 | Repton | In | Backhand | 59;34 | | | | | | | | | | | | | | | | | | | Unforced error | Middle inside | Neutral | Forehand | 70;36 | | | | | | | | | |
| SD advantage (1) | 523800 | 4880 | In | Error long | Wide | 64;31 | | | | | | | 20/08/2011 | Unforced error | 00:00 | Player 2 | SD advantage | Player | Serving | Set 1 | Advantage | Player 1 | 00:00 | Repton | Error | Backhand | 48;8 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| 0-0 (2) | 577560 | 5520 | In | Middle | Wide | 33;30 | | | | | | | 20/08/2011 | | 01:00 | Player 2 | 0-0 | Player | Returning | Set 1 | Deuce | Player 1 | 00:00 | Repton | In | Forehand | 59;30 | | | | | | | | | | | | | | | | | | | Unforced error | Middle inside | Attacking | Backhand | 57;37 | | | | | | | | | |
| 0-15 (1) | 609040 | 11800 | In | Middle | Body | 61;30 | | | | | | | 20/08/2011 | Winner | 01:00 | Player 2 | 0-15 | Player | Returning | Set 1 | Advantage | Player 1 | 00:00 | Repton | In | Backhand | 57;29 | | | | | | | | | | | | Player 1 | Middle baseline | Forehand | 28;27 | 1 | 1 | Ground stroke | In | Advantage wide | Attacking | Forehand | 75;37 | In | Middle baseline | Neutral | Forehand | 42;27 | | | | |
+------------------+----------+----------+-------------------+-------------------+------------------+------------+--------------------+-------------------+-------------------+-------------+------------------+------------+------------+--------------------+------------+-----------------+--------------+--------------+-----------+-----------+-----------+---------------+----------------+-----------------+--------------------+-------------------+-------------+----------------------+-----------------------+------------------------+---------------------+---------------------+----------------------+-----------------------+--------------------+---------------+-------------------+--------------+-----------+---------------------+--------------+-------------+---------------------------+-------------------------+---------------+---------------------+----------------------+-----------------------+--------------------+--------------+----------------------+-----------------------+------------------------+---------------------+---------------+---------------------+---------------------------+-------------------------+-----------------------+
将单元格求和到数组
- 这就是我目前的理解。请尝试并在评论中回复我，以解决可能的问题
代码
Option Explicit
Sub sumShotsInRally()
'Set rawData sheet as active
Dim ws As Worksheet
Set ws = Sheets("Input")
'Find first column to sum
Dim sForeCol As Long
sForeCol = ws.Rows(1).Find(What:="Serving player forehand", _
Lookat:=xlWhole).Column
'Add Rally Count Column.
Dim rallyCountCol As Long
rallyCountCol = sForeCol + 1
ws.Columns(rallyCountCol).Insert
ws.Cells(1, rallyCountCol).Value = "Rally Count"
'Find remaining columns to sum
Dim sBackCol As Long
sBackCol = ws.Rows(1).Find(What:="Serving player backhand", _
Lookat:=xlWhole).Column
Dim rForeCol As Long
rForeCol = ws.Rows(1).Find(What:="Returning player forehand", _
Lookat:=xlWhole).Column
Dim rBackCol As Long
rBackCol = ws.Rows(1).Find(What:="Returning player backhand", _
Lookat:=xlWhole).Column
'Define the range to iterate over as the used range of the found column
Dim sFore As Range
Dim sBack As Range
Dim rFore As Range
Dim rBack As Range
Dim RallyCount As Range
'Define column ranges
With ws
Set sFore = .Range(.Cells(2, sForeCol), _
.Cells(.UsedRange.Rows.Count, sForeCol))
Set sBack = .Range(.Cells(2, sBackCol), _
.Cells(.UsedRange.Rows.Count, sBackCol))
Set rFore = .Range(.Cells(2, rForeCol), _
.Cells(.UsedRange.Rows.Count, rForeCol))
Set rBack = .Range(.Cells(2, rBackCol), _
.Cells(.UsedRange.Rows.Count, rBackCol))
Set RallyCount = .Range(.Cells(2, rallyCountCol), _
.Cells(.UsedRange.Rows.Count, rallyCountCol))
End With
'Define Results Array
Dim Results As Variant
'You redimension the results array to the number of entries in your table
ReDim Results(1 To sFore.Rows.Count, 1 To 1)
'You loop over your table and sum the values from count and restocked
For i = 1 To sFore.Rows.Count
Results(i, 1) = sFore(i, 1) + sBack(i, 1) + rFore(i, 1) + rBack(i, 1)
Next i
'You write the array to the range count and delete the values in restocjed
RallyCount.Value = Results
End Sub