Hi Excel Community
I tried to find a solution on several boards but it seems I can't find it. Perhaps you can help me. First you need to know: I'm a noob regarding VBA.
Here the problem.
I have an excel file with thousands of rows and the column have headers.
In Column E I have dates with the format: dd/mm/yyyy. The range is between 5000 days in the past and 360 days in the future.
I need in column S the date format changed into text. So that e.g. 01/03/2021 would tell me: Monday
I have recorded a macro which resulted in this:
Range("S2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-14],""DDDD"")"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S25000"), Type:=xlFillDefault
Range("S2:S25000").Select
That is working but I rather would prefer something like this:
Dim LastRowColumnA As Long
Dim lr As Long, r As Long
Range("T2").Select
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("T2:T" & LastRowColumnA).Formula = "=IF(OR(F2=-12, F2=-11, F2=-10),""Yes"",""No"")"
Here I let Excel check for a date range (12, 11 and 10 days in the future as this is the crucial time frame)
It checks all rows and enters "Yes" or "No" depending on the conditions it finds in column F.
The file I create every day is changing in his size. So I don't have exactly 25k rows (as entered in my recorded macro). It can be more or less. To avoid extra work I would prefer that the macro is checking and filling in the date as a text in each cell of column S until the last row.
Can you please guide me through what I need to do?
Let me know if something is unclear.
Thanks for your time and effort.
Chris_Li
I tried to find a solution on several boards but it seems I can't find it. Perhaps you can help me. First you need to know: I'm a noob regarding VBA.
Here the problem.
I have an excel file with thousands of rows and the column have headers.
In Column E I have dates with the format: dd/mm/yyyy. The range is between 5000 days in the past and 360 days in the future.
I need in column S the date format changed into text. So that e.g. 01/03/2021 would tell me: Monday
I have recorded a macro which resulted in this:
Range("S2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-14],""DDDD"")"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S25000"), Type:=xlFillDefault
Range("S2:S25000").Select
That is working but I rather would prefer something like this:
Dim LastRowColumnA As Long
Dim lr As Long, r As Long
Range("T2").Select
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("T2:T" & LastRowColumnA).Formula = "=IF(OR(F2=-12, F2=-11, F2=-10),""Yes"",""No"")"
Here I let Excel check for a date range (12, 11 and 10 days in the future as this is the crucial time frame)
It checks all rows and enters "Yes" or "No" depending on the conditions it finds in column F.
The file I create every day is changing in his size. So I don't have exactly 25k rows (as entered in my recorded macro). It can be more or less. To avoid extra work I would prefer that the macro is checking and filling in the date as a text in each cell of column S until the last row.
Can you please guide me through what I need to do?
Let me know if something is unclear.
Thanks for your time and effort.
Chris_Li