title: Find non scientific notation value in excel
body: <p>I wish to find a cell with a certain value in Excel 2013 using vba. I have tried this which works fine as long as the column is wide enough.</p>
  
  <pre><code>Set foundCell = ThisWorkbook.Sheets(1).Rows(1).Find(What:="201402", LookIn:=xlValues)
  </code></pre>
  
  <p>However, if the column is not wide enough the number is shown in scientific notation, "2E+05", which will not be found by the above row, if I search for the value in non-scientific notation. This will work as long as the cell contains the value and not a formula:</p>
  
  <pre><code>Set foundCell = ThisWorkbook.Sheets(1).Rows(1).Find(What:="201402", LookIn:=xlFormulas)
  </code></pre>
  
  <p>However, in my case the cell value is a formula referencing another cell ("=C1"), in which case neither of the above will work.</p>
  
  <p>So, is it possible to search for "201402" and find it in row 2, in the following scenario?</p>
  
  <ol>
  <li>Create a new workbook in Excel 2013</li>
  <li>Insert 201402 in cell C1</li>
  <li>Insert =C1 in cell C2</li>
  <li>Make column C narrow enough so that scientific notation is shown</li>
  </ol>
  
  <p>Neither of this will work:</p>
  
  <pre><code>Set foundCell = ThisWorkbook.Sheets(1).Rows(2).Find(What:="201402", LookIn:=xlValues)
  Set foundCell = ThisWorkbook.Sheets(1).Rows(2).Find(What:="201402", LookIn:=xlFormulas)
  </code></pre>