title: VBA - finding column position within a range
body: <p>I have a set of contiguous data, say A1:F10.</p>
  
  <p>In B1:F1 are dates for the past week. My code searches for yesterday's date, then inserts a column to the right. In cells A2:A10 are names of fruit, the rest of the cells have random numbers of fruit. </p>
  
  <p>What I want to do is loop through all the fruits in Col A and enter a value (say 1) in the column I inserted to the right of yesterday's date if the fruit is an orange. The only ranges I can define are the A1:F10 using currentRegion and yesterday's date using entireColumn from .find.</p>
  
  <p>Does anyone know how to loop through col A and enter a value into the newly inserted column (which can be in a different place each time the sheet is generated)? I think it could be done using offset and some combination of columns/cells using the cell with yesterday's date in row 1 as a reference as the blank column will always be to the right of this.</p>
  
  <p>Thanks in advance and sorry for the complicated explanation.  </p>
  
  <pre><code>Dim myDate As Date
  
  myDate = Application.InputBox("Please enter the date (DD/MM/YY)")
  
  'Find yesterday's date in row 1
  
  Rows("1:1").Select
  Selection.Find(What:=myDate, After:=ActiveCell, LookIn:=xlFormulas _
      , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False).Activate
  
  Set Yesterday = ActiveCell
  
  For Each mycell In range("A:A")
  
    If Left(mycell.Value, 4) = "Oran" Then
  
        mycell.Offset(0, Not sure what to put here).Value = 1
  
    End If
  
  Next
  </code></pre>
  
  <p>Not sure what to put here = I think this is something to do with the yesterday variable +1 but not too sure</p>