0

I have list of data (one column with values) which I would like to set as Value in order to extract data from another range. I know that Value could only handel one cell, is there a workaround?

enter image description here

  • Step 1, Set a range of value in column G
  • Step 2, Find matching result in column A
  • Step 3, paste it in column G to L ( sometimes there will be more than one row of data for each matching reslut, hence Value X in step 1 may accur more than one time in step 2.)
Sub finddata() 
    Dim place As Long
    Dim FinalRow As Long
    Dim i As Integer

    Set place = Sheets(1).Range("h2:H10").Value

    FinalRow = Sheets(1).Range("A10000").End(xlUp).Row

    For i = 2 To FinalRow
        If Sheets(1).Cells(i, 1) = place Then
            Sheets(1).Range(Cells(i, 1), Cells(i, 5)).Copy
            Sheets(1).Range("H100").End(xlUp).Offset(1, 0).PasteSpecial       xlPasteFormulasAndNumberFormats
       End If
    Next i
End Sub

Ok this is how far I have reached now using autofilter. The problem now is that it's only filtering the firts row from the fetch list. In other words the the value from "G2"

 Sub hamta()




 Dim ary As Variant

 With Worksheets(1).Cells(1, 1).CurrentRegion
 'with true numbers in column A this DOES NOT work
   ary = Range("g2:g5").Value
   AutoFilter field:=1, Criteria1:=ary, Operator:=xlFilterValues
  'with true numbers in column A this DOES work

 End With

 End Sub
Martinswe
  • 43
  • 6
  • It's a bit unclear what you are trying to do. Probably it is a good idea to add screenshots to illustrate your issue. – Pᴇʜ Jan 14 '19 at 09:04
  • Make one more loop for each value within H2:H10, and nest the existing loop into it. – omegastripes Jan 14 '19 at 09:12
  • Ok, so basically this is just an example I'm showing with this screenshoot. The values below "fetch" should be matched and extracted below "Kostnad" "lag" and "lag2" It could be that these values in "fetch" could be represented more than ones, Then fetch all matching result for that value. Hope it's more clear now. – Martinswe Jan 14 '19 at 09:15
  • Possible duplicate of [Filter using array and xlFilterValues](https://stackoverflow.com/questions/42932353/filter-using-array-and-xlfiltervalues). There is no loop needed at all. In the `AutoFilter` use your `fetch` range as criteria array like `Criteria1:=Sheets(1).Range("h2:H10").Value` (`.Value` here returns an array of values). Also see [Range.AutoFilter method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofilter) and [xlAutoFilterOperator enumeration](https://learn.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator) – Pᴇʜ Jan 14 '19 at 09:19
  • Thanks! Since I'm a beginner how would a nested loop look lika in this case. XlFilterValues looks interesting too! – Martinswe Jan 14 '19 at 09:46
  • @Martinswe Don't use a loop. Since every value can occur multiple times in your data you need to filter anyway, so you can just filter for all data at once. Everything else would be much slower. – Pᴇʜ Jan 14 '19 at 09:52
  • Sub hamta() Tried this but only get the first value from the list I want to fetch Dim ary As Variant With Worksheets(1).Cells(1, 1).CurrentRegion 'with true numbers in column A this DOES NOT work ary = Range("g2:g5").Value . AutoFilter field:=1, Criteria1:=ary, Operator:=xlFilterValues 'with true numbers in column A this DOES work End With End Sub – Martinswe Jan 14 '19 at 13:10
  • @Martinswe sorry your comment is not readable. Please [edit] your question and put everything you want to ask there. Explain as detailed as possible. – Pᴇʜ Jan 14 '19 at 13:22
  • I Solved it by using application.Transpose. Lot of thanks to you! Really nice of you to help me with this – Martinswe Jan 14 '19 at 13:42

0 Answers0