0

I am self taught and relatively new to VBA! I have learned many things from your site, but now I am stuck.

I receive multiple files that have multiple columns of what should be dates. The files are originally exported in CSV format and opened in excel. Upon opening in any version of excel prior to Office 365, only those dates where the "day" is less than 12 ... are they recognized as dates, as the day gets written as the month. I have figured out how to run text to columns twice in order to correct the date(s) that are being imported (as per the VBA below) 

Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 3), TrailingMinusNumbers:=True

However, I have to specify the columns that contain dates, which I'm trying to avoid, assuming at some point the particular columns might change. I have found some VBA that will loop through and highlight columns based on headers - however I can't figure out how to apply the text to columns with those columns it finds that contain a date. I have been working with various code snippets that I have found on this site and others but am now stuck...

This code below works well to find the columns and do something with them, but what I need to do is use the above code instead of that which I've bolded below in the code ... as I'm not sure what it does, but it replaces the value in the correctly identified columns

The line I believe I need to replace is the following: mycell.Value = Val(mycell.Value) as this does something and replaces the appropriate columns with some value, but doesn't do what I want, i.e. taking the columns and running text to columns twice. The code as originally written is as follows: 

Sub FindAndConvert()
    Dim i           As Integer
    Dim lastRow     As Long
    Dim myRng       As Range
    Dim mycell      As Range
    Dim MyColl      As Collection
    Dim myIterator  As Variant

    Set MyColl = New Collection

    MyColl.Add "Birthdate"
    MyColl.Add "Collection Date"

    lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For i = 1 To 200
        For Each myIterator In MyColl
            If Cells(1, i) = myIterator Then
                Set myRng = Range(Cells(2, i), Cells(lastRow, i))
                For Each mycell In myRng


                    **mycell.Value = Val(mycell.Value)**


                Next
            End If
        Next
    Next
End Sub
treptara
  • 1

0 Answers0