Looping in macros

image how can i use looping in macros,where the condition is to it should loop up to sundays

Macros as in VBA?

yes,i need to fetch up to sundays based on datesimage i need to loop untill the condition met upto sundays

I don’t understand, do you want to do something after every 7th day or if it is a Sunday? This will set up your Macro to loop through and print the dates/days, just set the range then add an IF statement where it says “Add your code” and do what you need to.

Sub Test()

Dim st As Range
Dim x As Integer
Dim stDate As Date
Dim enDate As Date
Dim d As Date
Dim numRows as Long

NumRows = Range("F2", Range("F2").End(xlDown)).Rows.Count

For x = 0 To NumRows-2
'SET YOUR VARIABLES HERE
' This may seem redundant or unnecessary for this case, but it makes structuring nested
' loops easier to work with, and then there are fewer places to make changes, 
' if you need to make changes.

    Set st = Range("G2").Offset(x, 0)
    Set en = Range("D2").Offset(x, 0)
    stDate = DateSerial(Year(st), Month(st), Day(st))
    enDate = DateSerial(Year(en), Month(en), Day(en))

    'Then, loop through the dates as necessary
    For d = stDate To enDate
        Debug.Print d
        'Do your code here.
    Next

Next


End Sub

comparing with the 2 columns i need to fetch upto last sunday of the week(8.03.2020)
so i have to meet the condition that (do untill condition)if the condition met =sunday copy the dates from 1 to (8.03.2020)

1 Like

This code will do it then but will most likely need adapted if you are looking to use it in a full workflow where files are being passed or more complex datasets used. I have included a simple UiPath flow to help. Make sure to update the MyRange variable in UiPath which is passed to the Macro when applying this to any of your own test files.

The key here is VBA has an inbuilt function which helps check the day of a given date.

Sub Function(myRange as String)

Dim rng As Range
Dim cell As Range
Dim IsSunday As Boolean

Set rng = Worksheets("Sheet1").Range(myRange)

For Each cell In rng

    Select Case Weekday(cell.Value)
        Case vbSunday
            IsSunday = True
            MsgBox (Str(cell.Value) + " is a Sunday")
        Case Else
            IsSunday = False
            MsgBox (Str(cell.Value) + " is not a Sunday")
    End Select
    
Next cell

End Sub

TestDateDay.zip (19.7 KB)

2 Likes

In fact, you could to this exclusively in UiPath, without having to invoke or maintain VBA. I have added a UiPath option to the workflow:

TestDateDay_v2.zip (20.3 KB)

1 Like