How to use lookup formula in VBA code

[Jsejms]

I have used this VBA code to try automate my rostering process.
The roster template starts with the drivers being a 1 on their rostered day off work, when the roster is made the get assigned a route.

Sub Find_Replace_SHAYNE_NCO()

Range("D45:AS45").Replace What:="1", Replacement:="NCO"

End Sub


This code makes every 1 on Shaynes roster turn into a NCO.


How do I incorporate a lookup function to search for "SHAYNE" to determine what row this needs code needs to be applied to?
Future proofing for when I insert new rows and don't want to reexamine every macro.

Thanks

 

[Trebor76]

Jsejms,

Try this:

Option Explicit
Sub Macro1()

    Dim rngFind As Range
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Sheet name containing roster data. Change to suit if necessary.
    Set rngFind = ws.Cells.Find(What:="SHAYNE", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If rngFind Is Nothing Then
        MsgBox "There is no entry for SHAYNE in """ & ws.Name & """.", vbExclamation
        Application.ScreenUpdating = True
        Exit Sub
    Else
        ws.Range("D" & rngFind.Row & ":AS" & rngFind.Row).Replace What:="1", Replacement:="NCO"
        Application.ScreenUpdating = True
    End If

End Sub

Regards,

Robert

 

[Jsejms]

Jsejms,

Try this:

Option Explicit
Sub Macro1()

    Dim rngFind As Range
    Dim ws As Worksheet
   
    Application.ScreenUpdating = False
   
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Sheet name containing roster data. Change to suit if necessary.
    Set rngFind = ws.Cells.Find(What:="SHAYNE", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   
    If rngFind Is Nothing Then
        MsgBox "There is no entry for SHAYNE in """ & ws.Name & """.", vbExclamation
        Application.ScreenUpdating = True
        Exit Sub
    Else
        ws.Range("D" & rngFind.Row & ":AS" & rngFind.Row).Replace What:="1", Replacement:="NCO"
        Application.ScreenUpdating = True
    End If

End Sub

Regards,

Robert

Thanks for the input,

I have renamed the work sheet to "TEMPLATE"

When I run the macro it returns no "SHAYNE" found in template

 

[Jsejms]

Sorry, I realised I didn't add his full name to make it work.

Can I further complicate this when I have for example this code, running to alternate truck routes?

Sub Find_Replace_ALLAN_THOMPSON_NDS_NAN()

Range("D5:F5,O5:T5, AC5:AH5, AQ5:AS5").Replace What:="1", Replacement:="NDS"
Range("H5:M5, V5:AA5, AJ5:AO5").Replace What:="1", Replacement:="NAN"

End Sub

 

[Trebor76]

Can I further complicate this when I have for example this code, running to alternate truck routes?

You'll have to put in each like so:

Option Explicit
Sub Macro1()

    Dim rngFind As Range
    Dim ws As Worksheet
   
    Application.ScreenUpdating = False
   
    Set ws = ThisWorkbook.Sheets("Template") 'Sheet name containing roster data. Change to suit if necessary.
    Set rngFind = ws.Cells.Find(What:="SHAYNE", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   
    If rngFind Is Nothing Then
        MsgBox "There is no entry for SHAYNE in """ & ws.Name & """.", vbExclamation
        Application.ScreenUpdating = True
        Exit Sub
    Else
        ws.Range("D" & rngFind.Row & ":F" & rngFind.Row & ",O" & rngFind.Row & ":T" & rngFind.Row & ", AC" & rngFind.Row & ":AH" & rngFind.Row & ", AQ" & rngFind.Row & ":AS" & rngFind.Row).Replace What:="1", Replacement:="NDS"
        ws.Range("H" & rngFind.Row & ":M" & rngFind.Row & ", V" & rngFind.Row & ":AA" & rngFind.Row & ", AJ" & rngFind.Row & ":AO" & rngFind.Row).Replace What:="1", Replacement:="NAN"
        Application.ScreenUpdating = True
    End If

End Sub

Note though you probably don't need a separate macro for each worker but pass their name as a parameter into the macro when it's run.