Excel VBA loop for score card

Question

This one has me a bit stumped. Looking for a formula to help with a score card I'm doing for a tournament

Basically, if there is a valid name (word) in Column A and if there is a number in Column B, then copy that number to the next empty call in each row.

So, column A3 might have name Abe and B3 might have that Abe scored 20 points. Move the number 20 as a value only to the next empty cell in Row 3 between columns E & N. I need it to loop through all the names and scores in column A and B, skipping blanks

Not sure if it's important or not but columns A and B are populated using XLookup or some other index and if possible, ignore empty values.

Hope that's clear enough, sorry it's my 1st post so please let me know if I need to add more information.

Tried a bunch of formulas but nothing has come close to working

Answer 1

Update Table with New Values

Before

After

Sub UpdateWeeklyScores()

    ' Define constants.
    Const SCORE_FIRST_CELL_ADDRESS As String = "A3"
    Const SCORE_NAME_COLUMN As Long = 1
    Const SCORE_SCORE_COLUMN As Long = 2
    Const WEEK_FIRST_COLUMN As String = "E"
    Const WEEK_COLUMNS_COUNT As Long = 10
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Reference the ranges.
    Dim srg As Range: Set srg = ws _
        .Range(SCORE_FIRST_CELL_ADDRESS).CurrentRegion
    Dim RowsCount As Long: RowsCount = srg.Rows.Count
    Dim wrg As Range: Set wrg = srg.EntireRow _
        .Columns(WEEK_FIRST_COLUMN).Resize(, WEEK_COLUMNS_COUNT)
    
    ' Return the values of the ranges in arrays.
    Dim sData() As Variant: sData = srg.Value
    Dim wData() As Variant: wData = wrg.Value
    
    ' Declare additional variables.
    Dim Value As Variant, r As Long, c As Long, IsScoreValid As Boolean
    
    ' Loop through the rows of the arrays and apply the logic
    ' to update the values (scores) in the week array.
    For r = 1 To RowsCount
        ' Determine whether the score array's row values are valid.
        Value = sData(r, SCORE_NAME_COLUMN)
        IsScoreValid = False
        If Not IsError(Value) Then ' is no error
            If Len(CStr(Value)) > 0 Then ' is no blank
                Value = sData(r, SCORE_SCORE_COLUMN)
                If VarType(Value) = vbDouble Then ' is a number
                    IsScoreValid = True
                End If
            End If
        End If
        ' If the values are valid, write to the week array.
        If IsScoreValid Then
            ' Retrieve the last non-empty column.
            For c = WEEK_COLUMNS_COUNT To 1 Step -1
                If Not IsEmpty(wData(r, c)) Then Exit For
            Next c
            ' Add new score.
            If c = WEEK_COLUMNS_COUNT Then ' last column is not empty
                For c = 1 To WEEK_COLUMNS_COUNT - 1
                    wData(r, c) = wData(r, c + 1)
                Next c
            Else ' last column is empty
                c = c + 1
            End If
            ' Write.
            wData(r, c) = Value
        End If
    Next r
    
    ' Replace the values in the week range with the values in the week array.
    wrg.Value = wData
    
    ' Inform.
    MsgBox "Weekly scores updated.", vbInformation

End Sub