Find and replace 1st matching value of every row in a table

[cyberdimitri]

Hi,

I have a table which shows the monthly roster of my employees. Their shifts are marked as M1, M2, A1, A2, N1, N2 (depending on what time their shift starts), X for days-off and V for vacation.
https://ibb.co/pQdnH2p
(For simplicity I only put the first 6 days of the month in the pic).

I'm looking for a way to replace the first M2 of the month for each employee with A2.Is there any other way apart from doing it manually?

Using Excel 2010 with Windows 10.


Thanks for any help!

 

[sijpie]

Read the comments to understand

Option Explicit



Sub ReplaceFirstinMonth()
'// Replace first occurance of sFnd in a row _
 // with sRpl.
 
    Const sFnd As String = "M2"     '<<<< If you want to search for different value change here
    Const sRpl As String = "A1"     '<<<< If you want to replace with different value change here
    Dim vInp As Variant
    Dim lR As Long, lC As Long, lUB1 As Long, lUB2 As Long, lThis As Long, l1st As Long
    Dim rF As Range, rIn As Range, rSrch As Range, rThis As Range
    
    Set rIn = Range("A1").CurrentRegion
    
    
    'load month into an array for fast processing
    vInp = rIn.Value
    lUB1 = UBound(vInp, 1) 'get the number of rows
    lUB2 = UBound(vInp, 2) 'Get number columns
    
    'Now loop through each ro of the array finding the ist occurance of M2
    For lR = 1 To lUB1
        For lC = 1 To lUB2
            If vInp(lR, lC) = sFnd Then
                vInp(lR, lC) = sRpl     'replace the 1st occurance
                Exit For                'finished on this row, go to next row
            End If
        Next lC
        
    Next lR
    
    'Then put the result back in the worksheet
    rIn.Value = vInp
End Sub

 

[cyberdimitri]

Works great! Special thanks for the comments, they did help me understand what's going on.

 

[sijpie]

glad it works fine. I always comment my code, even short macro's, as you will forget very soon how you put it together, or what it was supposed to do. And then I still find I spend a lot of time trying to figure out how I made spmething work, when looking at old code... LOL