Loop code

[ChristineWisc]

I'm a complete novice at this, but have stumbled through up until this hiccup.

Trying to create code to check each cell in a column and if there is data in the cell, convert the data to a US date (currently coming in as text and European format). I got the date code to work in a different macro that has data in every cell of the column, but am stuck when it comes to running a check on each cell.

Here is what I'm trying and it will do the first cell, but then errors on the dte = Split(Left(cell.Text, InStr(cell.Text, " ") - 1), "/").

Sub EndDate_convert()
Dim cell As Range
'loop through each cell in a cell range
For Each cell In ActiveSheet.Range("i2:i65536")
If cell = 0 Then
'do nothing
Else
Dim dte() As String
dte = Split(Left(cell.Text, InStr(cell.Text, " ") - 1), "/")
cell.Value = DateSerial(dte(2), dte(1), dte(0)) + CDate(Mid(cell.Text, InStr(cell.Text, " ") + 1))
End If
Next cell
End Sub

Thanks.

 

[StephenCrump]

Re: Help with loop code

Welcome to the Forum!

Will your dates always be fixed length, e.g. will 1 April 2018 come in as '01/04/2018

or is it possible that it might come in as '1/4/2018?

I'm also not clear why you're using the Instr function. Your code implies that you're expecting strings like this:

'01/04/2018 SomethingElse

If so, what is the SomethingElse, and what do you want to do with it?

 

[Yongle]

Re: Help with loop code

- you may simply need to add this line to your code to allow VBA to ignore invalid values (could also add error handing to deal with each error)

On Error Resume Next

You did not specify exactly how the dates are presented
- here are 2 options that work for me (converted values are placed 5 columns to right - I prefer to not overwrite until fully tested)

Sub EndDate_convert1()
    '[COLOR=#ff0000]25/3/18[/COLOR]
    On Error Resume Next
    Dim cell As Range
    Dim dte() As String
'loop through each cell in a cell range
    For Each cell In Range("I2", Range("I" & Rows.Count).End(xlUp))
        If cell = 0 Then
    'do nothing
        Else
            dte = Split(cell.Text, "/")
            cell.Offset(, [COLOR=#000080]5[/COLOR]) = DateSerial(dte(2), dte(1), dte(0))
        End If
    Next cell
End Sub

Sub EndDate_convert2()
    '[COLOR=#ff0000]25 March 18[/COLOR]
    On Error Resume Next
    Dim cell As Range
    Dim dte() As String, mth As Integer
'loop through each cell in a cell range
    For Each cell In Range("I2", Range("I" & Rows.Count).End(xlUp))
        If cell = 0 Then
    'do nothing
        Else
            dte = Split(cell.Text, " ")
            mth = Month([B]DateValue[/B]("01-" & dte(1) & "-1900"))
            cell.Offset(,[COLOR=#000080] 5[/COLOR]) = DateSerial(dte(2), mth, dte(0))
        End If
    Next cell
End Sub

I am using UK Excel - hopefully also gives correct outcome for you - but you may need to switch construction of DateValue string to American

 

[ChristineWisc]

Re: Help with loop code

Thanks for trying to help me. As mentioned in my first note, I really have no idea what I am doing. I'm just trying to string things together until they work.

The dates come in as text and display date and time (but I don't care about the time).

28/10/2018 12:00:00 AM
28/10/2018 12:00:00 AM
29/10/2018 12:00:00 AM
29/10/2018 12:00:00 AM
29/10/2018 12:00:00 AM

I have one column in the spreadsheet where there is always a date in the cell and I use this macro to convert and it works beautifully.

Sub EUtoUSdate()
Range("e2", Range("e2").End(xlDown)).Select
Dim dte() As String, cell As Range
For Each cell In Selection
dte = Split(Left(cell.Text, InStr(cell.Text, " ") - 1), "/")
cell.Value = DateSerial(dte(2), dte(1), dte(0)) + CDate(Mid(cell.Text, InStr(cell.Text, " ") + 1))
Next
End Sub

So I need a similar function, but in a column where not every cell will have data. In the cells with data, they come in the same format as the other column and I need to convert to US date format (again, the time is not relevant and can drop off). So that is why I was experimenting with the loop function.

Thanks again.

 

[ChristineWisc]

Re: Help with loop code

Welcome to the Forum!

Will your dates always be fixed length, e.g. will 1 April 2018 come in as '01/04/2018

or is it possible that it might come in as '1/4/2018?

I'm also not clear why you're using the Instr function. Your code implies that you're expecting strings like this:

'01/04/2018 SomethingElse

If so, what is the SomethingElse, and what do you want to do with it?

Sorry, I didn't answer the first part of your question...

Dates appear to come in as always 2 digits in the middle and four digits on end, but the first (day) is coming in as both DD and D.

However, it appears the code used in the column that has a date in every cell, is handling that OK.

 

[ChristineWisc]

Re: Help with loop code

- you may simply need to add this line to your code to allow VBA to ignore invalid values (could also add error handing to deal with each error)

On Error Resume Next

You did not specify exactly how the dates are presented
- here are 2 options that work for me (converted values are placed 5 columns to right - I prefer to not overwrite until fully tested)

Sub EndDate_convert1()
    '[COLOR=#ff0000]25/3/18[/COLOR]
    On Error Resume Next
    Dim cell As Range
    Dim dte() As String
'loop through each cell in a cell range
    For Each cell In Range("I2", Range("I" & Rows.Count).End(xlUp))
        If cell = 0 Then
    'do nothing
        Else
            dte = Split(cell.Text, "/")
            cell.Offset(, [COLOR=#000080]5[/COLOR]) = DateSerial(dte(2), dte(1), dte(0))
        End If
    Next cell
End Sub

Sub EndDate_convert2()
    '[COLOR=#ff0000]25 March 18[/COLOR]
    On Error Resume Next
    Dim cell As Range
    Dim dte() As String, mth As Integer
'loop through each cell in a cell range
    For Each cell In Range("I2", Range("I" & Rows.Count).End(xlUp))
        If cell = 0 Then
    'do nothing
        Else
            dte = Split(cell.Text, " ")
            mth = Month([B]DateValue[/B]("01-" & dte(1) & "-1900"))
            cell.Offset(,[COLOR=#000080] 5[/COLOR]) = DateSerial(dte(2), mth, dte(0))
        End If
    Next cell
End Sub

I am using UK Excel - hopefully also gives correct outcome for you - but you may need to switch construction of DateValue string to American

Adding the On Error Resume Next worked!!!! Thank you so much. My code might be really ugly, but it is doing the job. So thankful I found this resource.

Thanks again!