[Solved] MLB BASEBALL FUTURE SCHEDULE( No Score Yet ) Updates

[Boy282828]

Dear Lutfor,



Last time you help me solving the Baseball MLB game score results problem,

it runs great success with only one click. However I can only run the after

game score results shown as expected, it will not run the future game schedule

since no score done yet. Could you modify the VBA Code such that once I put

vertical array in Column B of future game schedule, it will show the Visitor

team symbol in Column C, and Home team symbol in Column H? e.g.,

Change vertical array in Column B (Leave the original data intact)

2:20 pm Milwaukee Brewers @ Chicago Cubs Preview

6:10 pm Baltimore Orioles @ Cincinnati Reds Preview

6:40 pm San Francisco Giants @ Philadelphia Phillies Preview

To

Column C Column H (Get rid of Time and the Tail Preview)

MIL CHI

BAL CIN

SF PHI

All the scheduled MLB Baseball Game Data is from

https://www.baseball-reference.com/leagues/MLB-schedule.shtml

Thanks,

Boy282828 5/3/2024

 

[Lutfor Rahman Shimanto]

Dear Lutfor,



Last time you help me solving the Baseball MLB game score results problem,

it runs great success with only one click. However I can only run the after

game score results shown as expected, it will not run the future game schedule

since no score done yet. Could you modify the VBA Code such that once I put

vertical array in Column B of future game schedule, it will show the Visitor

team symbol in Column C, and Home team symbol in Column H? e.g.,

Change vertical array in Column B (Leave the original data intact)

2:20 pm Milwaukee Brewers @ Chicago Cubs Preview

6:10 pm Baltimore Orioles @ Cincinnati Reds Preview

6:40 pm San Francisco Giants @ Philadelphia Phillies Preview

To

Column C Column H (Get rid of Time and the Tail Preview)

MIL CHI

BAL CIN

SF PHI

All the scheduled MLB Baseball Game Data is from

https://www.baseball-reference.com/leagues/MLB-schedule.shtml

Thanks,

Boy282828 5/3/2024

Dear Dawson Chuang

Thanks for informing us that the previously given sub-procedure for the Baseball MLB game score results problem worked perfectly. Your appreciation means a lot to us.

I have reviewed your new requirements. Now, you want a sub-procedure that will perform on the game schedules that have yet to be played. Don't worry! I have developed such a sub-procedure and also have come up with a new idea called Event Procedure. Using the concept, you will not have to run the sub-procedure manually. It will automatically trigger when you have selected any cell within column B and get your job done.

SOLUTION Overview:

​

Follow these steps:

1. Right-click on the sheet name.
2. Click on View Code.
3. Insert the following code in the sheet module and save it:
   

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
       If Not Intersect(Target, Me.Columns("B")) Is Nothing Then
   
           If Target.Row = 1 Then
               Exit Sub
           Else
               Call NextAdvancedProcessBaseballData
           End If
           
       End If
   
   End Sub
   
   Sub NextAdvancedProcessBaseballData()
       
       Dim ws, wsTMS As Worksheet
       Dim selectedRange As Range
       Dim i As Long
       Dim teamSymbolDict As Object
       Dim tempText As String
       
       Set ws = ThisWorkbook.Worksheets("SKD DOG Next")
       Set wsTMS = ThisWorkbook.Worksheets("TMS")
       
       Set selectedRange = Application.Selection
       
       If selectedRange.Columns.Count > 1 Then
           MsgBox "More than one column has been selected.", vbCritical
           Exit Sub
       End If
       
       Set teamSymbolDict = CreateObject("Scripting.Dictionary")
       Dim teamSymbolRange As Range
       Set teamSymbolRange = wsTMS.ListObjects("TMSYMBOL").DataBodyRange
   
       Dim teamIndex As Long
       For teamIndex = 1 To teamSymbolRange.Rows.Count
           teamSymbolDict.Add teamSymbolRange.Cells(teamIndex, 1).Value, teamSymbolRange.Cells(teamIndex, 2).Value
       Next teamIndex
       
       For Each cell In selectedRange
           
           If cell.Value = "" Then
               GoTo endOfALoop
           End If
                   
           tempText = ReplaceChar(cell.Value)
                   
           Dim visitorTeam As String
           visitorTeam = Mid(tempText, InStr(tempText, ")") + 1, InStr(tempText, "@") - (InStr(tempText, ") ") + 1))
           
           visitorTeam = TrimSpaces(visitorTeam)
           
           Dim visitorSymbol As String
           visitorSymbol = teamSymbolDict(visitorTeam)
           
           ws.Range("C" & cell.Row).Value = visitorSymbol
           
           Dim homeTeam As String
           
           homeTeam = Mid(tempText, InStr(tempText, "@") + 1)
           
           homeTeam = TrimSpaces(homeTeam)
           
           Dim homeSymbol As String
           homeSymbol = teamSymbolDict(homeTeam)
           
           ws.Range("H" & cell.Row).Value = homeSymbol
           
   endOfALoop:
           
       Next cell
       
   End Sub
   
   Function TrimSpaces(ByVal inputString As String) As String
   
       While Left(inputString, 1) = " "
           inputString = Right(inputString, Len(inputString) - 1)
       Wend
   
       While Right(inputString, 1) = " "
           inputString = Left(inputString, Len(inputString) - 1)
       Wend
   
       TrimSpaces = inputString
   
   End Function
   
   Function ReplaceChar(ByVal inputString As String) As String
   
       Dim resultString As String
       Dim i As Integer
       Dim charCode As Integer
       
       For i = 1 To Len(inputString)
   
           charCode = Asc(Mid(inputString, i, 1))
           
           If charCode = 160 Then
               resultString = resultString & " "
           Else
               resultString = resultString & Mid(inputString, i, 1)
           End If
       Next i
   
       ReplaceChar = resultString
   
   End Function

4. Copy the intended data from the website.
5. Paste the data within the column B and get the result.

I hope the idea will help you to overcome your situation. I have attached the solution workbook as well; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[Boy282828]

Dear Lutfor,



The 2024 MLB Baseball Official Regular Season starts on 3/28/2024,

3/28/2024 is the official Begin Day, that is why all game schedule

before the Beginning Day, it labelled (Spring) which means Spring

Training Warmup games, it already happened but will not be listed

as OFFICIAL score between Team competition to decide which Team

is qualified for after regular season playoff games. They are Unofficial

games and it is not what I want. What I want is Regular Season Future

Scheduled Games to be played, not before the start of the season games.

The easiest way to find out the difference is to look for today’s date.

For example, if you find today’s date 5/5/2024 game schedule,

it may or may not have score results. Look next down the schedule,

you will find the future game schedule is something like

4:05 pm San Francisco Giants @ Philadelphia Phillies Preview

6:10 pm Detroit Tigers @ Cleveland Guardians Preview

6:40 pm Los Angeles Angels @ Pittsburgh Pirates Preview

Etc., on 5/6/2024

Could you modify the VBA Code to get rid of the Time in front and the Tail

“Preview” on back of the schedule, put the Away Team Symbol in Column C,

and Home Team Symbol in Column H respectively? I am sorry that I mislead

you to pay attention to before the official start day of Spring Training games,

I did not explain my goal clearly, it is my fault. Thanks,

Boy282828 5/5/2024

 

[Lutfor Rahman Shimanto]

Dear Lutfor,



The 2024 MLB Baseball Official Regular Season starts on 3/28/2024,

3/28/2024 is the official Begin Day, that is why all game schedule

before the Beginning Day, it labelled (Spring) which means Spring

Training Warmup games, it already happened but will not be listed

as OFFICIAL score between Team competition to decide which Team

is qualified for after regular season playoff games. They are Unofficial

games and it is not what I want. What I want is Regular Season Future

Scheduled Games to be played, not before the start of the season games.

The easiest way to find out the difference is to look for today’s date.

For example, if you find today’s date 5/5/2024 game schedule,

it may or may not have score results. Look next down the schedule,

you will find the future game schedule is something like

4:05 pm San Francisco Giants @ Philadelphia Phillies Preview

6:10 pm Detroit Tigers @ Cleveland Guardians Preview

6:40 pm Los Angeles Angels @ Pittsburgh Pirates Preview

Etc., on 5/6/2024

Could you modify the VBA Code to get rid of the Time in front and the Tail

“Preview” on back of the schedule, put the Away Team Symbol in Column C,

and Home Team Symbol in Column H respectively? I am sorry that I mislead

you to pay attention to before the official start day of Spring Training games,

I did not explain my goal clearly, it is my fault. Thanks,

Boy282828 5/5/2024

Dear Dawson Chuang

Thanks for your patience and for clarifying your requirements further. I understand your requirements and have modified the previously given sub-procedure accordingly.

SOLUTION Overview:

​

Excel VBA Sub-procedure:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Me.Columns("B")) Is Nothing Then

        If Target.Row = 1 Then
            Exit Sub
        Else
            Call NextAdvancedProcessBaseballData
        End If
        
    End If

End Sub

Sub NextAdvancedProcessBaseballData()
    
    Dim ws, wsTMS As Worksheet
    Dim selectedRange As Range
    Dim i As Long
    Dim teamSymbolDict As Object
    Dim tempText As String
    
    Set ws = ThisWorkbook.Worksheets("SKD DOG Next")
    Set wsTMS = ThisWorkbook.Worksheets("TMS")
    
    Set selectedRange = Application.Selection
    
    If selectedRange.Columns.Count > 1 Then
        MsgBox "More than one column has been selected.", vbCritical
        Exit Sub
    End If
    
    Set teamSymbolDict = CreateObject("Scripting.Dictionary")
    Dim teamSymbolRange As Range
    Set teamSymbolRange = wsTMS.ListObjects("TMSYMBOL").DataBodyRange

    Dim teamIndex As Long
    For teamIndex = 1 To teamSymbolRange.Rows.Count
        teamSymbolDict.Add teamSymbolRange.Cells(teamIndex, 1).Value, teamSymbolRange.Cells(teamIndex, 2).Value
    Next teamIndex
    
    For Each cell In selectedRange
        
        If cell.Value = "" Then
            GoTo endOfALoop
        End If
                
        tempText = ReplaceChar(cell.Value)
                
        Dim visitorTeam As String
        visitorTeam = Mid(tempText, InStr(tempText, "m ") + 1, InStr(tempText, "@") - (InStr(tempText, "m ") + 1))
        
        visitorTeam = TrimSpaces(visitorTeam)
        
        Dim visitorSymbol As String
        visitorSymbol = teamSymbolDict(visitorTeam)
        
        ws.Range("C" & cell.Row).Value = visitorSymbol
        
        Dim homeTeam As String
        
        homeTeam = Mid(tempText, InStr(tempText, "@") + 1, InStr(tempText, " Preview") - (InStr(tempText, "@") + 1))
        
        homeTeam = TrimSpaces(homeTeam)
        
        Dim homeSymbol As String
        homeSymbol = teamSymbolDict(homeTeam)
        
        ws.Range("H" & cell.Row).Value = homeSymbol
        
endOfALoop:
        
    Next cell
    
End Sub

Function TrimSpaces(ByVal inputString As String) As String

    While Left(inputString, 1) = " "
        inputString = Right(inputString, Len(inputString) - 1)
    Wend

    While Right(inputString, 1) = " "
        inputString = Left(inputString, Len(inputString) - 1)
    Wend

    TrimSpaces = inputString

End Function

Function ReplaceChar(ByVal inputString As String) As String

    Dim resultString As String
    Dim i As Integer
    Dim charCode As Integer
    
    For i = 1 To Len(inputString)

        charCode = Asc(Mid(inputString, i, 1))
        
        If charCode = 160 Then
            resultString = resultString & " "
        Else
            resultString = resultString & Mid(inputString, i, 1)
        End If
    Next i

    ReplaceChar = resultString

End Function

I hope you will find the sub-procedure helpful and fulfilling your goal. I have attached the solution workbook for better understanding; good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

 

[Boy282828]

Thanks Lutfor, it works perfect too.

Boy2828258 5/6/2024

 

[Lutfor Rahman Shimanto]

Thanks Lutfor, it works perfect too.

Boy2828258 5/6/2024

Dear Dawson Chuang, you are always welcome.

Regards
ExcelDemy