Solved: Autofill Cells When Another Cell Has Value

[coliervile]

Hello to all. I need a macro that will automatically fill cells N18 - W18 - AF18 - AO18 - AX18 - BG18 - BP18 - NY18 - CH18 with the values of cells C11 - C13 - C15 - C17 are filled with a number/text. On Sheet1 is an example of a baseball scorecard with innings, players names and players numbers. In the example in the first position (1) of the Batting Order player number 12 played innnings 1 and 2 then player number 12 was replaced by player number 8 and played in inning 3, then player number 8 was replaced by player number 6 and they are starting toplay in inning 4 and may or may not play the rest of the game. Here's the tricky part at the very beginning of the game player 12 is batting in the first innning I want cell N18 to automatically fill when the number 12 (player number) is entered in cell C11 and each subsequent inning that player 12 plays in. I then want cell AF18 to automatically fill when the number 8 (player number) is entered in cell C13 and each subsequent inning that player 8 plays in. I then want cell AO18 to automatically fill when the number 6 (player number) is entered in cell C15 and each subsequent inning that player 6 may play in. Whew is that complicated or what...hopefully I made it clear of what I need???

[Bob Phillips]

I think this event code does it



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:C"
Dim mpStart As Range

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .Row > 10 And .Row < 118 And .Row Mod 9 <> 1 Then

Set mpStart = Range("F" & (.Row \ 10 + 1) * 9)
End If

For i = 1 To 9

If mpStart.Offset(0, (i - 1) * 9 + 8).Value = "" Then

mpStart.Offset(0, (i - 1) * 9 + 8).Value = Target.Value
Exit For
End If
Next i
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[coliervile]

Thanks Bob for replying and good day to you. The macro does kind of what I want it too. It does insert the players number into the range of N18 - W18 - AF18 - AO18 - AX18 - BG18 - BP18 - NY18 - CH18 when a new player number is entered into Column "C". The thing it doesn't do, I'm not sure if it can be done, if a player plays in more that one inning it doesn't put their number into the adjacent inning... e.g. If player number 12 plays in innings 1 through 4 the macro puts the number 12 in cell N18 (inning 1) when put into cell C11 but not cells W18 - AF18 - AO18 (innings 2,3 and 4). That's the really tricky part and I'm not sure how to accomplish this if at all possible???

[Bob Phillips]

How would it know that player plays 4 innings before he plays them?

[coliervile]

That's the real issue and I don't think there's any way around it...

How would it know that player plays 4 innings before he plays them?

The code works great on Sheet1 but it'ss not working on the actual Scorecard. When I put your code in the actual worksheet I get a Compile error: Variable not defined at this location- For i = 1 To 9 I've included a copy of the actual Scorecard2 worksheet with this posting. Sheet1 was a copy of the Scorecard and was place in the same range as the original.

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C:C"
Dim mpStart As Range
Dim i As Long

On Error Goto ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .Row > 10 And .Row < 118 And .Row Mod 9 <> 1 Then

Set mpStart = Range("F" & (.Row \ 10 + 1) * 9)
End If

For i = 1 To 9

If mpStart.Offset(0, (i - 1) * 9 + 8).Value = "" Then

mpStart.Offset(0, (i - 1) * 9 + 8).Value = Target.Value
Exit For
End If
Next i
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[Bob Phillips]

What you could do is have a double-click event on those palyer numbers, and if the last player number inthe innings is the same, extend it by 1. It means a few double clicks, but it does keep it all contained.

[coliervile]

Here's the workbook had trouble uploading....

[coliervile]

Bob I just got your last two threads. You newer coding fixed the problem on the Scorecard worksheet, thanks. Can you give an example of what you're referring to about the Double_Click event with the workbook on thread #8?

[Bob Phillips]

This is what I mean.

I have pre-loaded C11 with 12.Try double-clicking it a coupl of times. Then put 8 in C13. Then double-click C11 again.

[coliervile]

:clap: Bob that's fantastic it was exactly what I needed. I didn't think it could have been done. :clever: ...once again your expertise amazes me. :bow:

:bigdance2

[Bob Phillips]

Easy tiger, it is only a bit of code. And it must be easy because one of your country-fellows thought that paying me more than $11 an hour for my services was not justified. As I told her, I don't get out of bed for that, it is less than minimum wage here, and my rate is *x.

[Bob Phillips]

BTW, I still think that formula should be VBA :devil2: . I concocted a similar formula, but it is far too messy, a maintainence nightmare, and will probably be as slow as dogs when you use it a lot.

[coliervile]

For the layperson (me) to even comprehend and think of the coding you concocted up with would be unheard of. That's the difference between being a Rookie and a Professional (you).

Are you referring to the formula from Mr. Excel??? I would like to have all of my formulas in VBA.

[Bob Phillips]

Indeed I am sir.

[coliervile]

Domenic surely came up with a nifty little formula there didn't he. Did you try to write a macro to emulate Domenic formula? The folks that help out on here on VBAX and Mr. Excel are great to work with and because all of you work with formulas and codes gives you a distinct advantage to think on a totally different level and plain that we (rookies) do beacause of you experience.