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)