macro only working on sheet2 in the same workbook

[excel_lelkes]

Dear Forum, please help me with the following problem:


I´ve a workbook with two sheets.
I wrote two macros for Sheet2
Both works perfect
my problem is that I can't get them working on Sheet 1
the NOT-working code:

Sub formateraDat()
Dim ws1 As Worksheet, lr As Long, i As Integer
Set ws1 = Sheet1
lr = ws1.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "lastrow" & " " & lr
For i = 2 To lr
ws1.Cells(i, 1).NumberFormat = ("yy-mm-dd")
Next i
End Sub

the working code:

Sub formateraDatum()
Dim ws2 As Worksheet, lr As Long, i As Integer

Set ws2 = Sheet2
lr = ws2.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr
ws2.Cells(i, 1).NumberFormat = ("yyyy-mm-dd")

Next i
End Sub

I've tried for hours without success
Please help!


Thank in advance
Regards
Peter

 

[MARK858]

If you put the formula

=ISNUMBER(A3)

in an empty cell on Sheet1 does it return TRUE or FALSE?

Btw, you don't need to loop through the cells, you can apply the numberformat to the range in one go.

Sub formateraDatum()
    Dim ws2 As Worksheet, lr As Long

    Set ws2 = Sheet2
    lr = ws2.Cells(Rows.Count, 1).End(xlUp).Row

    ws2.Range(ws2.Cells(2, 1), ws2.Cells(lr, 1)).NumberFormat = ("yyyy-mm-dd")

End Sub

 

[excel_lelkes]

Thanks Mark for your answer
it returns FALSE

…..Btw, you don't need to loop through the cells, you can apply the numberformat to the range in one go.

What´s the benefit of not using loop?

Regards
Peter

 

[MARK858]

Looping when interacting with the sheet slows the code down and takes more processing.

Because the formula is showing FALSE it means your dates aren't real dates but text.
Assuming that they are showing in your computers date format try selecting the column, clicking the Data Tab, click Text to Columns, click Next twice and then click Finish and see if the formula changes to TRUE.

 

[MARK858]

Just a very basic demo of the difference using loops try running the codes below.....

Sub xxxx()
    Dim t As Double, i As Long
    t = Timer
    For i = 1 To 10000
        Cells(i, 1).Value = 1
    Next
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub

Sub yyyy()
    Dim t As Double
    t = Timer
    Range("B1:B10000").Value = 1
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub

Obviously read my previous post about your current issue.

 

[excel_lelkes]

Obviously read my previous post about your current issue.

Evidently I failed writing the macro, because my purpose is to "automatically" convert text to number, so I can sort or import to google calendar

 

[MARK858]

Did my recommendation change the dates to real dates i.e the formula changed to TRUE?

 

[excel_lelkes]

Did my recommendation change the dates to real dates i.e the formula changed to TRUE?

yes

 

[MARK858]

So I take it the macro is now working ok?