Results 1 to 7 of 7

Thread: A loop which finds and matches more than one value - VBA

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default A loop which finds and matches more than one value - VBA

    Hi all. I need help with some VBA using find and match type queries. I have a worksheet which has columns: ID, test depth and test result. I have a second worksheet which as ID, start depth, end depth and category. What I need the code to do is loop through each row in the first worksheet and check to find the ID in the second worksheet. Then see if the test result depth (the second col) for that row falls between the start depth and end depths (second and third cols in the other worksheet). If it matches the ID but the test depth isn’t between the two depths on that row, then it moves on to the next row. If it matches the ID and the test depth in between the values in col 2 and 3, then the code should return the category of that row (col 4) to col 3 of the first worksheet.
    Sheet 1
    ID Test Depth Test Result VBA Response
    BH1 0.75 6 Type A
    BH1 3.8 1 Type B
    BH1 1.11 25 Type A

    Sheet 2
    ID Start Depth End Depth Category
    BH1 0 1.45 Type A
    BH1 1.45 4 Type B
    BH1 4 5 Type C

    Hope that’s clear. Thanks in advance!

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Location
    Kuwait
    Posts
    449
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A loop which finds and matches more than one value - VBA

    Hey

    Try the below VBA code ... Just make sure to have a header in sheet1 column D before running the macro

    Code:
    Sub FindCategory()
    Dim Ar1 As Variant, Ar2 As Variant, Ws1 As Worksheet, Ws2 As Worksheet
    Set Ws1 = Sheet1 ' make sure to change the sheet name
    Set Ws2 = Sheet2 ' make sure to change the sheet name
    Ar1 = Ws1.UsedRange.Value2
    Ar2 = Ws2.UsedRange.Value2
    For x = 2 To UBound(Ar1)
        For y = 2 To UBound(Ar2)
            If Ar1(x, 1) = Ar2(y, 1) And Ar1(x, 2) >= Ar2(y, 2) And Ar1(x, 2) <= Ar2(y, 3) Then
                Ar1(x, 4) = Ar2(y, 4)
                Exit For
            End If
        Next y
    Next x
    Ws1.Range("A1").Resize(UBound(Ar1), UBound(Ar1, 2)) = Ar1
    End Sub

    Check the List of BB codes


  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: A loop which finds and matches more than one value - VBA

    Quote Originally Posted by Storm8585 View Post
    Hope that’s clear.
    Not quite.
    What result would you want (& why) for BH1 and Depth 4?

    BTW, do you really need a macro as this could be done with standard worksheet formulas?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,203
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: A loop which finds and matches more than one value - VBA

    Quote Originally Posted by Storm8585 View Post
    Hi all. I need help with some VBA using find and match type queries. I have a worksheet which has columns: ID, test depth and test result. I have a second worksheet which as ID, start depth, end depth and category. What I need the code to do is loop through each row in the first worksheet and check to find the ID in the second worksheet. Then see if the test result depth (the second col) for that row falls between the start depth and end depths (second and third cols in the other worksheet). If it matches the ID but the test depth isn’t between the two depths on that row, then it moves on to the next row. If it matches the ID and the test depth in between the values in col 2 and 3, then the code should return the category of that row (col 4) to col 3 of the first worksheet.

    According to your example, the result is in column 4.

    Sheet 1
    ID Test Depth Test Result VBA Response
    BH1 0.75 6 Type A
    BH1 3.8 1 Type B
    BH1 1.11 25 Type A

    Sheet 2
    ID Start Depth End Depth Category
    BH1 0 1.45 Type A
    BH1 1.45 4 Type B
    BH1 4 5 Type C

    Hope that’s clear. Thanks in advance!
    Continuing with Peter's comment, if you fix the duplicate values ​​on sheet2, it can be with a formula:

    Sheet1

     ABCD
    1IDTest DepthTest ResultVBA Response
    2BH10.75 Type A
    3BH13.8 Type B
    4BH11.11 Type A
    5BH14 Type C
    6BH15 Type C
    7BH16 No Match

    CellFormula
    D2=IFERROR(INDEX(Sheet2!$D$2:$D$4,SUMPRODUCT((Sheet2!$A$2:$A$4=A2)*(Sheet2!$B$2:$B$4<=B2)*(Sheet2!$C$2:$C$4>=B2)*(ROW(Shee t2!$D$2:$D$4)))-1),"No Match")


    -----------------------------------


    Sheet2 With proposed values ​​to avoid the ambiguity of the values.

     ABCD
    1IDStart DepthEnd DepthCategory
    2BH101.45Type A
    3BH11.463.9Type B
    4BH145Type C



    ----------------------------------

    But if you also want the macro:

    Code:
    Sub Macro1()
      With Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = _
            "=IFERROR(INDEX(Sheet2!R2C4:R4C4,SUMPRODUCT((Sheet2!R2C1:R4C1=RC[-3])*(Sheet2!R2C2:R4C2<=RC[-2])*(Sheet2!R2C3:R4C3>=RC[-2])*(ROW(Sheet2!R2C4:R4C4)))-1),""No Match"")"
        .Value = .Value
      End With
    End Sub
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A loop which finds and matches more than one value - VBA

    All many thanks for the advise and solutions - I am very grateful for your help!

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,972
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: A loop which finds and matches more than one value - VBA

    Quote Originally Posted by Storm8585 View Post
    All many thanks for the advise and solutions - I am very grateful for your help!
    Glad you got a successful outcome but I would still be interested to know ..
    - Whether you used formula or macro, and
    -
    Quote Originally Posted by Peter_SSs View Post
    What result would you want (& why) for BH1 and Depth 4?
    I am asking this question because with your original sample data BH1, Depth 4 fits both of the last 2 rows in your sample Sheet2 meaning it could be Type B or Type C
    Last edited by Peter_SSs; Aug 8th, 2019 at 09:35 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,203
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: A loop which finds and matches more than one value - VBA

    Quote Originally Posted by Storm8585 View Post
    All many thanks for the advise and solutions - I am very grateful for your help!
    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •