unfortunately your advice seems not to work as a proper solution for this problem.
I attach an excelsheet with two tabs: 'data' and ' output'.
and a straightforward macro-code:
Sub Problemwithdateformat2()
Application.ScreenUpdating = False
Sheets("Data").Select
Dim StartDate, EndDate, MyDate As Date
StartDate = Cells(3, 16)
EndDate = Cells(4, 16)
MyDate = StartDate
Range(Cells(1, 2), Cells(64000, 2)).NumberFormat = "dd-mm-yyyy"
Do Until MyDate > EndDate
Row = 2
Do Until Cells(Row, 2) = MyDate
Row = Row + 1
Loop
Row2 = Row
Do Until Cells(Row2, 2) <> MyDate
Row2 = Row2 + 1
Loop
' I need values for row and row2 to select and manipulate a range of data from the same date being MyDate.
' I leave those calculationsteps out of the code because it is not relevant
' the result of the procedure are values for Hedgeloss and CumTurnover, which i will give values of 1 and 2 respectively for simplicity.
Hedgeloss = 1
CumTurnover = 2
OutputArray = Array(MyDate, Hedgeloss, CumTurnover)
' i copy the output for this date to a separate sheet. the output from previous dates are replaced 1 row lower.
Sheets("Output").Select
Columns("A:A").NumberFormat = "dd-mm-yyyy"
Range("A8:BB10000").Copy
Range("A9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A8:C8") = OutputArray
' now i set MyDate 1 higher to calculate Hedgeloss and Cumturnover for the next date.
MyDate = MyDate + 1
Sheets("Data").Select
Loop
End Sub
Now, my problem: as you can see in column A of the 'Output'-sheet, the dates are reformatted during the VBA-procedure into mm/dd/yyyy format. in other words: mm and dd are mixed.
Undoubtedly I am unaware of a small piece of code that prevends this. Who could help me?
Thanks