title: Excel VBA: Find columns with dates, return column value if found
body: <p>I'll keep it quick and honest, I am developing some code in order to find column references against peoples names if they have a financial figure associated with them.</p>
  
  <p>Below I have a set of tables, one which outlines the original spreadsheet, and the other which is what I am expecting this code to do, i have written the code to find matching date entries on the two sheets, and return the column number of the date against the names which ONLY contain values.  The rest are ignored, however the code doesn't paste anything into column D as it shows below, and I get no errors.  The resulting entry against all the names always ends up being 16384, can anybody see any errors with my code?</p>
  
  <p>See below for sheet examples and code.  Its worth noting that the original sheet is a pivot table.</p>
  
  <pre><code>                    Original Sheet
         A   |    B     |    C     |    D     |
    1________|__________|__________|__________|
    2________|__________|__________|__________|
    3________|__________|__________|__________|
    4________|__________|__________|__________|
    5        |01/01/2015|01/02/2015|01/03/2015|
     ________|__________|__________|__________|     
    6 henry  |   $200   |          |    $300  |
      _______|__________|__________|__________|
    7 luke   |   $100   |   $250   |          |
      _______|__________|__________|__________|
    8 michael|   $300   |          |          |
      _______|__________|__________|__________|
    9 james  |          |   $250   |          |
      _______|__________|__________|__________|
  
  
         Sheet with Column values where the original figures existed
  
         A   |    B     |    C     |    D     |
    1________|__________|__________|__________|
    2________|__________|__________|__________|
    3________|__________|__________|__________|
    4________|__________|__________|__________|     
    5________|__________|__________|__________|
    6 henry  |    2     |          |    4     |
      _______|__________|__________|__________|
    7 luke   |    2     |     3    |          |
      _______|__________|__________|__________|
    8 michael|    2     |          |          |
      _______|__________|__________|__________|
    9 james  |          |     3    |          |
      _______|__________|__________|__________|
  </code></pre>
  
  <p><strong>Code begins here</strong></p>
  
  <pre><code>Sub MapValues
  Dim namerow As Long, actualrow As Long, actualcolumnsource As Long, actualcolumntarget As Long
  Dim actualcost As Range
  Dim actualcostvalue As String
  Dim J As Long
  Set CapexSourceSheet = RngSource.Worksheets("Capex Pivot")
  Set CapexTargetSheet = RngDest.Worksheets("Capex")
  
  Set dumpsheet = RngSource.Worksheets.Add
  dumpsheet.Name = "Dump Sheet"
  dumpsheet.Range("A1").Value = "Name"
  dumpsheet.Range("B1").Value = "Source Row Number"
  dumpsheet.Range("C1").Value = "Target Row Number"
  dumpsheet.Range("D1").Value = "Actuals"
  
  'Set ranges and arrays for the find function
  dumpsheet.Activate
  namerow = CapexSourceSheet.Cells(rows.Count, 1).End(xlUp).row
  actualcolumnsource = ActiveSheet.Cells(5, Columns.Count).End(xlToRight).row
  CapexTargetSheet.Activate
  actualcolumntarget = ActiveSheet.Cells(5, Columns.Count).End(xlToRight).Column
  
  lngCnt = 2
  
  ' Loop takes the date from the original sheet, and checks the target sheet for the date 
  ' column, if it finds a match, it should return the column number.  Instead it returns
  ' nothing
  
  
  CapexSourceSheet.Activate
  
  For J = 1 To actualcolumnsource
         actualcostvalue = CapexSourceSheet.Cells(5, actualcolumnsource).Value
            CapexTargetSheet.Activate
              Set actualcost = ActiveSheet.Cells(5, actualcolumntarget).Find(What:=actualcostvalue, _
                                                                        LookIn:=xlFormulas, _
                                                                        LookAt:=xlPart, _
                                                                        SearchOrder:=xlByColumns, _
                                                                        SearchDirection:=xlNext, _
                                                                        MatchCase:=False, _
                                                                        SearchFormat:=False)
              If Not actualcost Is Nothing Then
              lngCnt = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
                 Do While lngCnt &gt; 0
                 If actualcost.Value = CapexTargetSheet.Cells(5, actualcolumntarget).Value Then
                    dumpsheet.Cells(lngCnt, 4) = actualcost.Column
                 End If
                 lngCnt = lngCnt - 1
                 Loop
              End If
              actualcolumnsource = actualcolumnsource - 1
  Next J
  </code></pre>
  
  <p><strong>UPDATE</strong>: The 'sourcename' variable can pick up the dates in the source sheet, but it cannot seem to find them in the target sheet.  In the source sheet, the dates are written in text, but in the target sheet the dates are not free text, they are formulas (example, 01/03/2015 is written as =+DATE(YEAR(AB5),MONTH(AB5)+1,1)) where the value in AB5 is 01/02/2015.  Could this affect the outcome in the .FIND method?</p>