CODEWITHSUNDEEP
excelvba
Gaaras
Gaaras

Reputation: 1

Excel VBA Replace Date Format

Hallo i need a little help. First sry for any mistakes in my language i will try my best to descripe my problem but i am not a native speaker.

I want to replace every cell in Range("U2:U9999")in my active Sheet (Tabelle2) with the date 31.12.9999 with the date of today. The format of the cells shouldn´t change. Everything from the code works beside this little problem. I´m not an expert in vba so hopfully someone can help me.

VBA Code:

Sub DateiOeffnen()
Dim wb As Workbook
Dim strOpenFile As Variant
strOpenFile = Application.GetOpenFilename(, , "Bitte die Exceldatei auswählen:")
If strOpenFile = False Then Exit Sub
Set wb = Workbooks.Open(strOpenFile, UpdateLinks:=0, ReadOnly:=False)
ActiveSheet.Name = "Tabelle1"
Sheets("Tabelle1").Activate
ActiveSheet.UsedRange.Copy
ThisWorkbook.Activate
Sheets("Tabelle2").Activate
Range("A1:S9999").Select
ActiveSheet.Paste
ActiveSheet.FindFormat.NumberFormat = "dd/mm/yyyy;@"
Range("U1:U9999").Select
    Selection.Replace What:="31.12.9999", Replacement:="Date", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
        ReplaceFormat:=False
ActiveSheet.Range("R2:R9999").FormulaLocal = "=WENN(U2="""";"""";WENN(T2="""";"""";WENN(U2-T2<365;1;"""")))"
wb.Close False
Sheets("Quotenberechnung").Activate
End Sub

Upvotes: 0

Views: 90

Answers (1)

Sam
Sam

Reputation: 736

what about something like this

for i = 2 to 999

Worksheets("your sheet name goes here").range("U" & i).value = "31.12.9999"

next i

Upvotes: 0

Related Questions