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

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


.

Thanks losmac , but this code does not loop through worksheet "Scores" and
somehow does not run the code to calculate the number of goal from worksheet
"Data"


"losmac" wrote in message
...
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


.