Find and Replace VBA Depending on Day of Week

[FrenchCelt]

Hello,

I'm coding a macro of which part of what I need to accomplish is a series of Find and Replace functions where it will pick between two options depending on what day of the week it is, the value of which is displayed in cell F2.

For example, if the cell contains text that says "REC_10A or REC_12A", I want to replace this with "REC_10A" on Tuesdays, Wednesdays, Thursdays, and Fridays, and "REC_12A" on Saturdays, Sundays, and Mondays.

Does anyone know if this can be done?

Any help is greatly appreciated.

 

[DanteAmor]

Tuesdays, Wednesdays, Thursdays, and Fridays, ... Saturdays, Sundays, and Mondays.

And in which cells are the days

Do you literally have the names of the days or is it a date?

You could exemplify it. Use xl2bb tool.

Note XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[FrenchCelt]

The date is displayed in Cell F2 as dddd, mmmm dd, yyyy format. The data to do the selective Find and Replace depending on day of the week is in F1.

I can't use foreign add-in tools on my work computer. Our IT policies are extremely strict.

This is what it looks like, though:

So in this case, I would like to have VBA code that will convert the text in F1 to just LDPA_12A because the day in F2 is on either a Saturday Sunday, or Monday. But if it was Tuesday through Friday, it would say LDPA_10A. I can code a basic Find and Replace that will convert that text to either LDPA_10A or LDPA_12A, but then I need to add menu functions that will ask the macro user what shift and department the report is being run on, and since the data is right there, I'd like to take out the need to key in anything when running the macro.

 

[DanteAmor]

Try this:

Sub Find_and_Replace()
  Dim d As Variant
  
  If IsDate(Range("F2").Value) Then
    d = Weekday(Range("F2").Value)
    Select Case d
      Case 1, 2, 7
        Range("F1").Value = "LDPA_12A"
      Case 3 To 6
        Range("F1").Value = "LDPA_10A"
    End Select
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[FrenchCelt]

That worked perfectly, but it looks like it would only work for that one case. Is there a way to open up the range to include other departments? I have multiple possible combinations:

REC_10A or REC_12A
REC_10B or REC_12B
LDPA_10A or LDPA_12A
LDPA_10B or LDPA_12B
OF_10A or OF_12A
OF_10B or OF_12B
SHIP_10A or SHIP_12A
SHIP_10B or SHIP_12B
ECOM_10A or ECOM_12A
ECOM_10B or ECOM_12B

Is there a way to adapt the code so that whatever text string appears in F1, it will lead to the correct result based on day of week? All 10A/10B results go with Tuesday-Friday and all 12A/12B results go with Saturday, Sunday, and Monday.

 

[DanteAmor]

Is there a way to adapt the code so that whatever text string appears in F1, it will lead to the correct result based on day of week? All 10A/10B results go with Tuesday-Friday and all 12A/12B results go with Saturday, Sunday, and Monday.

Try this:

Sub Find_and_Replace()
  Dim d As Variant, txt2 As Variant
  Dim txt As String, txt3 As String, sufix As String
  Dim n As Long
  
  If IsDate(Range("F2").Value) Then
    d = Weekday(Range("F2").Value)
    txt = " " & Range("F1")
    n = InStr(1, txt, "_10A", vbTextCompare)
    If n > 0 Then
      sufix = "A"
    Else
      n = InStr(1, txt, "_10B", vbTextCompare)
      If n > 0 Then
        sufix = "B"
      End If
    End If
    If n > 0 Then
      
      txt2 = Split(Left(txt, n - 1), " ")
      txt3 = txt2(UBound(txt2))
      
      Select Case d
        Case 1, 2, 7
          Range("F1").Value = txt3 & "_12" & sufix
        Case 3 To 6
          Range("F1").Value = txt3 & "_10" & sufix
      End Select
    End If
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[FrenchCelt]

Unfortunately that new code had no effect. The text remained unchanged.

Edit: This case only had LDPA_12A in the string, because LDPA_10A had no data when I ran the report. On a hunch, I went and added "LDPA_10A or" ahead of LDPA_12A and it worked. Sorry to throw another kink into the works, but I guess I need to account for the possibility that sometimes it will be only one or the other and not both.

 

[DanteAmor]

Sorry to throw another kink into the works

Dont worry try this:

Sub Find_and_Replace()
  Dim d As Variant, txt2 As Variant
  Dim txt As String, txt3 As String, sufix As String
  Dim n As Long
  
  If IsDate(Range("F2").Value) Then
    d = Weekday(Range("F2").Value)
    txt = " " & Range("F1")
    n = InStr(1, txt, "_10A", vbTextCompare)
    
    If n > 0 Then
      sufix = "A"
    Else
      n = InStr(1, txt, "_12A", vbTextCompare)
      If n > 0 Then
        sufix = "A"
      Else
        n = InStr(1, txt, "_10B", vbTextCompare)
        If n > 0 Then
          sufix = "B"
        Else
          n = InStr(1, txt, "_12B", vbTextCompare)
          If n > 0 Then
            sufix = "B"
          End If
        End If
      End If
    End If
    If n > 0 Then
      
      txt2 = Split(Left(txt, n - 1), " ")
      txt3 = txt2(UBound(txt2))
      
      Select Case d
        Case 1, 2, 7
          Range("F1").Value = txt3 & "_12" & sufix
        Case 3 To 6
          Range("F1").Value = txt3 & "_10" & sufix
      End Select
    End If
  End If
End Sub

Do you have a list of all the possible apartments?
That would help and that way it would work for any department.
Let me know if you have a list of all the departments and if it is possible to put them on one sheet in one column, for example:

varios 26sep2023.xlsm
	A	B
1	DEPTS
2	REC
3	LDPA
4	OF
5	SHIP
6	ECOM
7
Depts

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[FrenchCelt]

Sorry it took me so long to reply. I had to go on a work trip and wasn't able to give the new code a try until today. It works exactly as needed, so thank you very much!