title: copy rows in range with specific value in column F
body: <p>I have an worksheet with lots of columns and also lots rows. from this worksheet I want to copy the rows which match 2 conditions:
  1. the value in column B must match the selected value from a dropdown list in a different worksheet
  2. the value in column F must match with the selected value from a different dropdown list.</p>
  
  <p>I have a script that works for condition one.</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Dim fRow As Integer, lRow As Integer
  Dim value As String
  Dim mychart As chart
  Dim mycharts As ChartObject
  
  If ActiveCell.Address = Sheets("blad1").Cells(1, 1).Address Then
  
  Sheets("chartdata").Cells.ClearContents
  
  For Each ChartObject In Sheets("blad3").ChartObjects
  ChartObject.Delete
  Next
  
  value = Sheets("blad1").Cells(1, 1).value
  
  With Sheets("schaduwblad")
  fRow = .Range("B:B").find(what:=value, after:=Range("B1")).Row
  lRow = .Range("B:B").find(what:=value, after:=Range("B1"), lookat:=xlWhole, searchdirection:=xlPrevious).Row
  .Range("B1:DT1").Copy _
  Sheets("chartdata").Range("A1")
  .Range("B" &amp; fRow, "DT" &amp; lRow).Copy _
  Sheets("chartdata").Range("A2")
  
  
    With Sheets("blad3")
    Set mychart = .Shapes.AddChart.chart
  
      With mychart
        .SetSourceData Source:=Sheets("chartdata").Range("B1").CurrentRegion
        .ChartType = xlLine
        .HasTitle = True
        .HasLegend = True
  
        With .ChartTitle
        .Text = "=Blad1!R1C1"
        .AutoScaleFont = False
        .Font.FontStyle = "verdana"
  
        End With
        With mychart.Legend
  
          .FontSize = 8
          .Position = xlLegendPositionBottom
          .AutoScaleFont = False
          .Font.FontStyle = "verdana"
          .FontSize = 8
        End With
  
      End With
    End With
  End With
  End If
  End Sub
  </code></pre>
  
  <p>But I cannot create the script that is needed to also match condition 2.</p>
  
  <p>Here is a screenshot from the structure the document has:
  <a href="https://i.stack.imgur.com/Kl3ub.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Kl3ub.png" alt="https://i.imgsafe.org/5e7034c.png"></a><br>
  <sub>(source: <a href="https://i.imgsafe.org/5e7034c.png" rel="nofollow noreferrer">imgsafe.org</a>)</sub>  </p>
  
  <p>The first condition is that the match with the value in column B. That is a closed range which can easily be copied. 
  But the second condition uses the value in column F, which is changing every row.</p>
  
  <p>For example, based on the screenshot, I want to select all the rows which have the value NL Food in column B and the Omzet (x 1000) in column F. (so rows which have Verkopen (x1000) in verpakkingen) have to be excluded from the selection.</p>
  
  <p>(the choice for omzet (x 1.000) or Verpakking (x 1.000) is also made using a drop down list).</p>
  
  <p>How can I make VBA to only select the rows which meet both conditions?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I was able to change the data layout so that now FCT is in column B directly after MKT. This way, all data is first sorted on MKT and after that on FCT so my data layout should make it easier to select the area which matches both conditions, as it is one closed range.
  <a href="https://i.stack.imgur.com/grMc2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/grMc2.png" alt="http://i.imgsafe.org/00db13c.png"></a></p>
  
  <p>Therefore, I thought I was able to change the code and have both conditions met.</p>
  
  <p>I added a <em>frow2</em> and <em>lrow2</em> which now have to find the <em>value2</em> parameter in column B. However, with the code which is posted below, I get an Error 13 message saying "types do not match". I do not understand why that is. I guess it has something to do with the way I defined the search range for frow2 and lrow2.</p>
  
  <p>Part of the adjusted code is below, I added the italic lines</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Dim fRow As Integer, lRow As Integer, frow2 As Integer, lrow2 As Integer
  
  Dim value As String
  Dim value2 As String
  Dim mychart As chart
  Dim mycharts As ChartObject
  
  If ActiveCell.Address = Sheets("blad1").Cells(1, 1).Address Then
  
  Sheets("chartdata").Cells.ClearContents
  
  For Each ChartObject In Sheets("blad3").ChartObjects
  ChartObject.Delete
  Next
  
  value = Sheets("blad1").Cells(1, 1).value
  value2 = Sheets("blad1").Cells(1, 3).value
  
  With Sheets("schaduwblad")
  fRow = .Range("A:A").find(what:=value, after:=Range("A1")).Row
  lRow = .Range("A:A").find(what:=value, after:=Range("A1"), lookat:=xlWhole, searchdirection:=xlPrevious).Row
  frow2 = .Range(.Cells(fRow, 2), .Cells(lRow, 2)).find(what:=value2, after:=Range("B1"), lookat:=xlWhole).Row
  lrow2 = .Range(.Cells(fRow, 2), .Cells(lRow, 2)).find(what:=value2, after:=Range("B1"), lookat:=xlWhole, searchdirection:=xlPrevious).Row
  .Range("E1:DS1").Copy
  Sheets("chartdata").Range("A1")
  .Range("E" &amp; fRow, "DS" &amp; lrow2).Copy_
  Sheets("chartdata").Range("A2")_
  </code></pre>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>I tried this line (see below)  to find out why I get the error 13.</p>
  
  <pre><code>frow2 = .Range("B:B").find(what:=value2, after:=Range("B1"), lookat:=xlWhole).Row
  </code></pre>
  
  <p>Where I use the entire column B as search range. This works fine for the find methode. 
  As soon as I change the range to anything else I get the error 13 message: types do not match. </p>
  
  <p>It seems the <em>range.find</em> method cannot work with ranges which are defined more then just an entire column? (e.g. B2:B41).</p>
  
  <p><strong>Edit 3:</strong> The reason I got the error 13 message was that I searched in a range for example B2:B41 and in the find. parameters I entered B1 as find.after range. I changed it now like this and it works:</p>
  
  <pre><code>frow2 = .Range(.Cells(fRow, 2), .Cells(lRow, 2)).find(what:=value2, after:=Range("B" &amp; fRow), lookat:=xlWhole).Row
  lrow2 = .Range(.Cells(fRow, 2), .Cells(lRow, 2)).find(what:=value2, after:=Range("B" &amp; fRow), lookat:=xlWhole, searchdirection:=xlPrevious).Row
  </code></pre>