Issues with cell type conversion

[TdotW]

Hi, I'm having difficulty with excel's automatic recognition of cell formats.

I have a column imported from another source that are stored in 'general' format - numbers are stored as text, dates appear as 'dd/mm/yyyy.

I am assigning these values to another column in a separate sheet, however excel appears to automatically detect date data types incorrectly as mm/dd/yyyy.

This leads to issues with dates such 30/01/YYYY and therefore returns a numeric value.

Dim a
For irow = 4 To LastRow
    a = Cells(irow, 2).Value
        For icol = 3 To LastColumn
        If (Cells(irow, icol).Value > 0) Then
            Cells(irow, icol).Value = a
        End If
    Next icol
Next irow

'a' is the general variable, the cell it's assigned to has the auto-detection issue.

thanks for any help.

 

[Joe4]

Are you able to import this column as Text? Then you can have better control over the data, and explicitly perform VBA code on it to do what you want.