Auto fill?? Help??

  • Thread starter Thread starter herman
  • Start date Start date
H

herman

I have in a column a range of cells label tyke #1 thru to
Tyke # 8 (A2:A9) columns (B1:CE1) are labelled as days of
the week. the range (B2:CE9) is the schedule for the teams
I would like that when I enter "P2" in any column (b1:ce1)
on row A2 that it automatically know to put "P1" in row A3
and if I enter "H3" in row A8 it automatically puts "G7" in
row A4 and vice versa. Basically as soon as I enter either
"P1-8" or "H1-8" or "G1-8" in any cell with the
range(B2:CE9) it will put the corresponding entry in the
same column. (note; no team can practice or play against
itself) I have a spread sheet with over 4000 games
practices to do and if someone out there knows how to do
this ican modify it to make it work for all levels that i
hae to schedule. thanks
 
Herman

I have in a column a range of cells label tyke #1 thru to
Tyke # 8 (A2:A9) columns (B1:CE1) are labelled as days of
the week.
Click to expand...

Like Monday, Tuesday, Wednesday, or actual dates/
the range (B2:CE9) is the schedule for the teams
I would like that when I enter "P2" in any column (b1:ce1)
on row A2 that it automatically know to put "P1" in row A3
and if I enter "H3" in row A8 it automatically puts "G7" in
row A4 and vice versa.
Click to expand...

Please explain. Does P stand for practice and G for game? If so, what is
H? I assume by entering P2, that means that Tyke#2 is the other side and
you want the corresponding entry on their row. Why then would you put P1 on
row 3? Wouldn't you want Px where x is the team on whose row you're
entering? And why if you enter H3, would it put G7 and not H7?
Basically as soon as I enter either
"P1-8" or "H1-8" or "G1-8" in any cell with the
range(B2:CE9) it will put the corresponding entry in the
same column. (note; no team can practice or play against
itself) I have a spread sheet with over 4000 games
practices to do and if someone out there knows how to do
this ican modify it to make it work for all levels that i
hae to schedule. thanks
Click to expand...

This looks like 82 columns and 8 rows which would be 656 games, not 4000, so
I'm a little confused on that point.

If all my assumptions are correct, then try this. Right click on sheet tab
and choose View Code, then paste this in the code window that pops up. Let
me know if it's even close.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim OtherRow As Long
Dim ThisTeam As Long
Dim cell As Range

Application.EnableEvents = False

'Only cells in the right range
If Not Intersect(Target, Me.Range("B2:CE9")) Is Nothing Then

'If cell is deleted
If Not IsEmpty(Target) Then

'Make sure you enter the right format
If Target.Value Like "[G,H,P]#" Then

OtherRow = CLng(Right(Target.Value, 1)) + 1
ThisTeam = Target.Row - 1

With Me.Cells(OtherRow, Target.Column)
If IsEmpty(.Item(1)) Then
.Value = Left(Target.Value, 1) & ThisTeam
Else
MsgBox "Team " & Me.Cells(OtherRow, 1).Value & _
" is already scheduled"
Target.ClearContents
End If
End With
Else
MsgBox "Invalid entry"
Target.ClearContents
End If
Else
For Each cell In Intersect(Target.EntireColumn, _
Me.Range("b2:CE9")).Cells

If Right(cell.Value, 1) = CStr(Target.Row - 1) Then
cell.ClearContents
Exit For
End If
Next cell
End If
End If

Application.EnableEvents = True

End Sub
 
Back
Top