Run code from one worksheet to populate another.
Public Sub SumGoals()
Dim cellRow As String, firstAddress As String
Dim mScorer As String
Dim mTotal As Integer
Dim mGoals As Integer
Dim myCell As Range
Dim c As Object
mTotal = 0
cellRow = ActiveCell.Row
mScorer = ActiveSheet.Range("A" & cellRow)
With Worksheets("Data").Range("AD3:AL500")
For Each c In Range("AD2:AL500")
If c.Value = mScorer Then
firstAddress = c.Address
mGoals = c.Offset(0, 1).Value
mTotal = mTotal + mGoals
End If
Next c
End With
If mTotal 0 Then
ActiveSheet.Range("A" & cellRow).Offset(0, 3).Value =
mTotal
End If
End Sub
-----Original Message-----
I have 2 worksheets in the same workbook called "DATA"
and "Scores".
Structure of sheet "Scores" is as follows
A B C
1 Player Caps goals
2 Beckham 65
3 Shearer 67
4 Beattie 2
I need to populate column C with number of goals scored
by each player
(Column A) from worksheet "DATA". The code below runs
fine when I select the
player (Range A) to become the ActiveCell and then run
the code and only if
the code and the three column are in the same worksheet,
which is "sheet
"DATA".
I need to add a loop to the code to run it from
sheet "scores". Of course
the line
mScorer = ActiveCell
needs to be changed to reflect going through all the
players
Public Sub SumGoals()
Dim mScorer As String
Dim mTotal As Integer
Dim mGoals As Integer
Dim myCell As Range
mTotal = 0
mScorer = ActiveCell
With Worksheets("Data").Range("AD3:AL500")
For Each c In Range("AD2:AL500")
If c.Value = mScorer Then
firstAddress = c.Address
mGoals = c.Offset(0, 1).Value
mTotal = mTotal + mGoals
End If
Next c
End With
If mTotal 0 Then
ActiveCell.Offset(0, 3).Value = mTotal
End If
End Sub
.
|