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

Boy282828

New member
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 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:
MLB BASEBALL FUTURE SCHEDULE( No Score Yet ) Updates.gif

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:
    Code:
    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
 

Attachments

  • 2024MLB.xlsm
    29.8 KB · Views: 2
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 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:
MLB BASEBALL FUTURE SCHEDULE( No Score Yet ) Updates.gif

Excel VBA Sub-procedure:
Code:
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
 

Attachments

  • 2024MLB.xlsm
    30.1 KB · Views: 1

Online statistics

Members online
0
Guests online
68
Total visitors
68

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top