Date Concatenation VBA

Question

I am trying to concatenate but it is not working

Dim i As Integer
          For i = 2 To lastRow
          If Not IsDate(wsFinal.Cells(i, 17).Value) Then
            With wsFinal
                .Cells(i, 17).NumberFormat = "#"
                .Cells(i, 17).Value = "01/01/" & wsFinal.Cells(i, 17).Value
            End With
       End If
       Next i

For example, if I have a number stored as text (1949) I am trying to change the format to number which is not a date so it can be looped through. Then I want to add that year to 01/01/xxxx. CAn anyone tell me whats wrong?

Answer 1

Try using the dd/mm/yyyy Number format. # is for Number values.

Dim i As Integer
For i = 2 To lastRow
    If Not IsDate(wsFinal.Cells(i, 17).Value) Then
        With wsFinal
            .Cells(i, 17).NumberFormat = "dd/mm/yyyy"
            .Cells(i, 17).Value = "01/01/" & wsFinal.Cells(i, 17).Value
        End With
    End If
Next i

Also make sure wsFinal.Cells(i, 17) actually returns a year.

IsEmpty is not a function for testing values. It is more about testing variables, like Arrays or objects to see if they are initialized.

Example is,

Dim iCtr 
Debug.Print IsEmpty(iCtr)   '<- Will Print True
iCtr = Null
Debug.Print IsEmpty(iCtr)   '<- Will Print False

In the above example, we have initialized the variable iCtr as Null, although on a common world it is still not a value, it still is initialized. So IsEmpty will not yield the right result. It might be better to test with IsNull. However Zero Length String are not really Null, so IsNull("") will fail, when iCtr = vbNullString. So in hindsight, IsEmpty and IsNull might not do the trick.

What I said about using IsDate (make sure wsFinal.Cells(i, 17) actually returns a year) is, if you test IsDate("Happy"), the result is going to be False, so the condition you are checking will Pass as it is Not IsDate("Hello"), so make sure it is returning a number and not Hello or some other string, which is not a Date.

In conclusion, take extra care which function you use. I would recommend a Not IsDate followed by a IsNumeric