title: VBA find row number , then use loop to find new cell with value in
body: <p>I've got a workbook "Vendor Management Simplified8" with the data included in the "Overdue PO" worksheet and the organisation chart "Organisation Chart" in another worksheet <a href="https://i.stack.imgur.com/AzSIo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AzSIo.png" alt="Org Chart"></a>. Within the data sheet there is a list of people who manage the contractors. I want to loop through each of these names (There are no blank spaces in this column) and find their names within the Organisation chart and return the General Managers name and Department and return these values into columns N and O. The names may not be found in the organisation chart.</p>
  
  <p>So far I've got this ... </p>
  
  <pre><code>Dim Cell As String
  Dim loopy As Integer
  Dim findrow As range
  Dim finddata As Integer
  
  loopy = 2
  Do Until Worksheets("Overdue PO").range("E" &amp; loopy).VALUE = "" 'loops through the name of person managing contractor
      Cell = Trim(Worksheets("Overdue PO").range("E" &amp; loopy).VALUE) 'value which needs to be looked up
  
  'finds the row number where value is found
      Set findrow = Worksheets("Organisation Chart").range("G:L").find(What:=Cell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
  
      If findrow Is Nothing Then
      Else: finddata = findrow.Row
      End If
  
          Do Until Worksheets("Organisation Chart").range("D" &amp; finddata).VALUE &lt;&gt; "" 'if cell is not empty
              If Worksheets("Organisation Chart").range("D" &amp; finddata).VALUE = "" Then
                  finddata = finddata - 1 'go up the cells
              End If
          Loop
  
      Worksheets("Overdue PO").range("0" &amp; loopy).VALUE = Worksheets("Organisation Chart").range("D" &amp; finddata).VALUE 'department
      Worksheets("Overdue PO").range("N" &amp; loopy).VALUE = Worksheets("Organisation Chart").range("D" &amp; finddata - 1).VALUE 'general manager
      loopy = loopy + 1
  
  Loop
  </code></pre>
  
  <p>So far - it finds the value and returns the row number in 'findrow', it loops up the rows in the Organisation Chart until the value isn't blank, then an error appears (run-time error 1004 - Application defined or object-defined error appears) on line <code>Worksheets("Overdue PO").range("0" &amp; loopy).VALUE = Worksheets("Organisation Chart").range("D" &amp; finddata).VALUE</code> when trying to enter the department into column O of the Overdue PO worksheet. </p>
  
  <p>What am I doing wrong for to get this error? Is there an easier way to loop through up the column to find the General manager and department?</p>
  
  <p>Thanks in advance:)</p>