title: VBA: Find column header and reformat (date) values in columns
body: <p>I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below). </p>
  
  <p>Now, all I want is to add VBA code in the existing macro to find a column header (say,  "Application date") and then convert all the dates into YYYY-MM-DD format.  The original values in this column don't have a fixed date format.</p>
  
  <pre><code>Public Sub ExportToCsvFile(FName As String, _
      Sep As String, SelectionOnly As Boolean, _
      AppendDataOnExistingFile As Boolean)
  
  Dim WholeLine As String
  Dim FNum As Integer
  Dim RowNdx As Long
  Dim ColNdx As Integer
  Dim FirstRow As Long
  Dim LastRow As Long
  Dim FirstCol As Integer
  Dim LastCol As Integer
  Dim CellValue As String
  
  
  Application.ScreenUpdating = False
  On Error GoTo EndMacro:
  FNum = FreeFile
  
  If SelectionOnly = True Then
      With Selection
          FirstRow = .Cells(1).Row
          FirstCol = .Cells(1).Column
          LastRow = .Cells(.Cells.Count).Row
          LastCol = .Cells(.Cells.Count).Column
      End With
  Else
      With ActiveSheet.UsedRange
          FirstRow = .Cells(1).Row
          FirstCol = .Cells(1).Column
          LastRow = .Cells(.Cells.Count).Row
          LastCol = .Cells(.Cells.Count).Column
      End With
  End If
  
  If AppendDataOnExistingFile = True Then
      Open FName For Append Access Write As #FNum
  Else
      Open FName For Output Access Write As #FNum
  End If
  
  For RowNdx = FirstRow To LastRow
      WholeLine = ""
      For ColNdx = FirstCol To LastCol
          If Cells(RowNdx, ColNdx).Value = "" Then
              CellValue = Chr(34) &amp; Chr(34)
          Else
             CellValue = Cells(RowNdx, ColNdx).Value
             CellValue = Replace(Replace(CellValue, Chr(150), Chr(45)), Chr(151), Chr(45))
             CellValue = Replace(Replace(CellValue, Chr(60), Chr(60) &amp; Chr(32)), Chr(10), "&lt;br /&gt;")
             CellValue = Chr(34) &amp; Replace(CellValue, Chr(34), Chr(34) &amp; Chr(34)) &amp; Chr(34)
          End If
          WholeLine = WholeLine &amp; CellValue &amp; Sep
      Next ColNdx
      WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
      Print #FNum, WholeLine
  Next RowNdx
  
  EndMacro:
  On Error GoTo 0
  Application.ScreenUpdating = True
  Close #FNum
  
  End Sub
  
  Sub ExportToSemiColonCsv()
      Dim FileName As Variant
      Dim Sep As String
      FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="CSV Files (*.csv),*.csv")
      If FileName = False Then
          Exit Sub
      End If
  
      ExportToCsvFile FName:=CStr(FileName), Sep:=";", _
         SelectionOnly:=False, AppendDataOnExistingFile:=True
  End Sub
  </code></pre>
  
  <p>I couldn't find any old question that can actually point me in right direction.  Thanks in advance.</p>
  
  <p><strong>UPDATE 1:</strong>
  Already tried FORMAT(CellValue,"yyyy-mm-dd") without success.</p>
  
  <p><strong>Update 2:</strong>
  Also tried reading the cell value in a string variable and then converting it back into date type like this:</p>
  
  <pre><code>If IsDate(CellValue) Then
        Dim str1 As String
        str1 = Cells(RowNdx, ColNdx).Value
        Dim DateValue As Date
        DateValue = CDate(str1)
        CellValue = Format(DateValue, "yyyy-mm-dd")
  End If
  </code></pre>
  
  <p>No use,  in fact, it seems that code to is not executing (no compile error though).</p>
  
  <p><strong>Update 3:</strong>
  Finally,  I'm able to solve it using <code>.NumberFormat</code> and <code>.Text</code>. See my answer below for final code and the limitations.</p>