generate day then show 2nd previous weekday

[k1dr0ck]

hi,

how do you
when opening the workbook generate constant day of 7,14,21 based on the current month and the 2nd to the last weekday of the current month on B1 to B4
if the values of B1 to B4 is a weekend (sat-sun) then the date value in B will change to the previous weekday based on its original date value
then
the user will manually edit date values of G1 to G5 (not all cells will have date values everytime)
then
after updating G1 to G5 (using sheet change trigger)
it will check if the values of B1 to B4 if it is in the values of G1 to G5
if true, the value in B will change to the previous weekday based on its date value
(possibility of date values in B will change twice)
then
from date value of column B it will show the 2nd previous weekday in column C
it will check if the values of C1 to C4 if it is in the values of G1 to G5
if true, the value in C will change to the previous weekday based on its date value

attached sample file with before and after sheets

[Marc L]

when opening the workbook

As a beginner starter, paste next event code to the module ThisWorkbook
then clear cells B1:B4 of Sheet1, save the workbook and close it to see what happens when you re-open it …

Private Sub Workbook_Open()
        Dim M%, Y%, R%, D As Date, W%
            M = Month(Date)
            Y = Year(Date)
    With Sheet1
        For R = 1 To 4
            With .Cells(R, 2)
                If .Value2 = "" Then
                    D = DateSerial(Y, M - (R = 4), IIf(R = 4, 1, R * 7)) + (R = 4) * 2
                    W = Weekday(D, 2) - 5
                    If W > 0 Then D = D - W
                   .Value = D
                End If
            End With
        Next
    End With
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[k1dr0ck]

hi,

thanks for the reply
it tried it and it adds the dates to B1 to B4

[Marc L]

As it was the answer to first part « when opening the workbook » …
Is the result correct, the one you expect for ?

[k1dr0ck]

yes, it is correct
thank you

[k1dr0ck]

so i tried to understand the macro code of Marc L so i can apply it to comparing B to G and C to G
but its too advanced for me :D

i came up with the below code it works but only for B1 to G1
i can compare it (B1,B2,B3,B4) to (G1,G2,G3,G4,G5)
then show previous weekday in C
then compare (C1,C2,C3,C4) to (G1,G2,G3,G4,G5)
but then it would take a same block of code for each date value comparison
so it would be a long code

how do i make a much shorter code for all comparison?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wkdayb, wkdayc, wkdayg, nwdtb, nwdtc
Dim dateb, datec

If Range("B1").Value = Range("G1").Value Then
rptwkdy:
dateb = Range("B1").Value
dateb = DateAdd("d", -1, dateb)
Range("B1").Value = dateb

wkdayb = Format(Range("B1").Value, "ddd")
If wkdayb = "Sat" Or wkdayb = "Sun" Then
GoTo rptwkdy
End If
End If

End Sub

[k1dr0ck]

so i found this code and trying to understand the code in the blue and its comparing range of A to B
if i just can understand it i can apply it from my problem of comparing B to G
my question is
how does it compare A to B since it does not show in the code the reference to column B?

Sub CompareLists()
Dim Rng As Range, RngList As Object

Set RngList = CreateObject("Scripting.Dictionary")

'''Make a list of the ColumnB items...
For Each Rng In Range("B1", Range("B" & Rows.Count).End(xlUp))
  If Not RngList.Exists(Rng.Value) Then
    RngList.Add Rng.Value, Nothing
  End If
Next

'''Go through Col.A and test for existance of each value in Col.B
'''(Highlight items in Column A that are NOT found in Column B)
For Each Rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
  If Not RngList.Exists(Rng.Value) Then
    Rng.Font.ColorIndex = 3
  End If

End Sub
Next

[Marc L]

Via the easy Excel way :

Sub Part2()
    With Sheet1
        .[B1:B4].Value = .[IF({1},B1:B4-ISNUMBER(MATCH(B1:B4,G1:G5,0)))]
        .[C1:C4].Value = .[IF({1},B1:B4-2-(WEEKDAY(B1:B4,2)=1)*2)]
        .[D1:D4].Value = .[IF({1},WEEKDAY(C1:C4,2)-5)]
        .[C1:C4].Value = .[IF(D1:D4>0,C1:C4-D1:D4,C1:C4)]
        .[D1:D4].Clear
        .[C1:C4].Value = .[IF({1},C1:C4-ISNUMBER(MATCH(C1:C4,G1:G5,0)))]
    End With
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[k1dr0ck]

hi,

thanks for the code and the tip