VB Loop to Mark Used Data

[Ricker090]

I need help with a macro that I wrote. I have two data ranges; one is a list of awarded training spot and the other a list of numbers that represent all the available training spots. I wrote a loop to put an "X" next to each spot that is used. It doesn't seem to work the way I want it to though. For example, if you look at August 10th, there were only two awarded spots, but my current macro puts an "X" next to all the spots on August 10th.

I going insane trying to figure this out. Any suggestions are appreciated. Also, if there's a better and more efficient way to write this macro, I'm open to suggestions.

I have attached my workbook.

[davegugg]

I'd suggest this amendment to your code:

Sub Used_Spaces()

    Dim srcR, destR, L As Integer
    
    For srcR = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        If IsNumeric(Cells(srcR, 1).Value) Then
            L = Cells(srcR, 1).Value
            For destR = 2 To Cells(Rows.Count, 5).End(xlUp).Row
                If L = Cells(destR, 4).Value And Cells(destR, 8).Value = "" Then
                    Cells(destR, 7).Value = "X"
                    Exit For
                End If
            Next destR
        End If
    Next srcR

End Sub

First, find ways to not use "On Error Resume Next". This can lead to all sorts of problems. To make the code work without it, I check each cell in column A to make sure it is a number before attempting to reserve a spot with it.
Next to make sure only one matching spot is Xed, I add an Exit For which will get out of the inner For loop once a cell has been marked X. In general it is bad to use Exit For, but for this simple code it is easier than the alternative.

[Ricker090]

Excelent! Thank you for the advise. It's very much appreciated. I learn something new everytime I log into this forum.