Data Update

[nuttygolfer]

I do not consider myself a programmer, simply an excel user.
Using Excel for our golf club the following macro is employed:
Sub EnterPoints()

Dim TodaysPoints As Integer
Dim NewPoints As Integer
Dim CurrentPoints As Integer

If (Range("B4") <> "") And (Range("A4") <> "") Then
TodaysPoints = Range("A4")
If Range("H4") = "ESTABLISHING" Then
NewPoints = TodaysPoints
Else
CurrentPoints = Range("H4")
If CurrentPoints - TodaysPoints > 5 Then
NewPoints = CurrentPoints - 5
Else
NewPoints = TodaysPoints
End If
End If
Range("A4") = NewPoints
Range("A4").Select
Selection.Copy
Select Case Range("J4")'J4 contains the games played
'using Count(C4:G4)
Case "0": Range("C4").Select
Case "1": Range("D4").Select
Case "2": Range("E4").Select
Case "3": Range("F4").Select
Case "4": Range("G4").Select
Case "5": Range("D4:G4").Select
Selection.Copy
Range("C4").Select
ActiveSheet.Paste
Range("A4").Select
Selection.Copy
Range("G4").Select
End Select
ActiveSheet.Paste
End If

The macro looks a B4 to see if there is a members name present and if so checks A4 to see if there are points to be added.
H4 is looked at to see if the member is establishing a point base or already has one.
If the player has a point base the new score (A4) is checked for a minimum decrease of 5 points.
After all this the new points to be added is placed in A4.
Acording to amount of games played the case statement places the new points in the correct cell.
This if statement is repeated 50 times (once for each player).
I have tried to use a sub similar to sort.Calling Selection.UpdatePoints
where the range is A4:A54 Key1:=Range("A4") Order1:=x1Ascending

My problem is how can I develop a generic sub and avoid 50 very similar case statements.

It might be helpful to know that cell H4 contains the following if statement:
=IF((ISBLANK(B4)),"",(IF((J4>2),(IF(AVERAGE(C4:G4)<7,"7",AVERAGE(C4:G4))),"ESTABLISHING")))
Any assistance will be greatly appreciated. If you know any publication that will help me.

 

[CautionMP]

If I am reading correctly you want to run the same macro for 50 (more or less) rows?

The easiest way (minimal re-writting of code, changes in bold):

Sub EnterPoints()

Dim TodaysPoints As Integer
Dim NewPoints As Integer
Dim CurrentPoints As Integer
[b]Dim CurrentRow As Integer[/b]

[b]For CurrentRow = 4 To 54[/b]
  [green]'Step through rows 4 to 54 and use the current number
  'as a replacement in all you Ranges below[/green]
  If (Range("B" [b]& CurrentRow[/b]) <> "") And _
     (Range("A" [b]& CurrentRow[/b]) <> "") Then
     TodaysPoints = Range("A" [b]& CurrentRow[/b])
     If Range("H" [b]& CurrentRow[/b]) = "ESTABLISHING" Then
        NewPoints = TodaysPoints
     Else
       CurrentPoints = Range("H" [b]& CurrentRow[/b])
       If CurrentPoints - TodaysPoints > 5 Then
          NewPoints = CurrentPoints - 5
       Else
          NewPoints = TodaysPoints
       End If
     End If
     Range("A" [b]& CurrentRow[/b]) = NewPoints
     Range("A" [b]& CurrentRow[/b]).Select
     Selection.Copy
     Select Case Range("J" [b]& CurrentRow[/b]) [green]'J4 contains _
     the games played using Count(C4:G4)[/green]
       Case "0": Range("C" [b]& CurrentRow[/b]).Select
       Case "1": Range("D" [b]& CurrentRow[/b]).Select
       Case "2": Range("E" [b]& CurrentRow[/b]).Select
       Case "3": Range("F" [b]& CurrentRow[/b]).Select
       Case "4" [b]& CurrentRow[/b]: Range("G" [b]& CurrentRow[/b]).Select
       Case "5": Range("D4:G" [b]& CurrentRow[/b]).Select
                  Selection.Copy
                  Range("C" [b]& CurrentRow[/b]).Select
                  ActiveSheet.Paste
                  Range("A" [b]& CurrentRow[/b]).Select
                  Selection.Copy
                  Range("G" [b]& CurrentRow[/b]).Select
    End Select
    ActiveSheet.Paste
  End If
[b]Next CurrentRow[/b]
End Sub

Short story CurrentRow becomes a pointer to indicate the row being processed. To use in a [tt]Range()[/tt] statement it gets CONCATENATEd to the column lable using an &.

If you want to turn this into an exercise in macro programming post back and we can delve into the Excel object model.

Hope this helps,
CMP

Instant programmer, just add coffee.