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 dates 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)
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)
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)