Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Need help with Find Method

  1. #11
    Board Regular
    Join Date
    Jun 2005
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with Find Method

    The first entry in the For Loop is actually: 2013, 6.615384615, 0002, 11 for Year, Avg, UIC and Group respectively. When I run the above code for Group 11, the value $CH$1 is placed in the cell with row labelled 0002 (FindUIC) and column labelled 2013 (FindYear). This is just the address for FindGroup and not the value for Avg.
    Last edited by ouadad; Oct 1st, 2019 at 01:28 PM.

  2. #12
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need help with Find Method

    Can you just run the code below and just confirm that the addresses printed are the expected/wanted cells?

    Code:
    Sub Friedman_TestX()
    Dim year As Integer, avg As Double, factor As String, group As Integer, iRow As Long
    Dim FindUIC As Range, FindGroup As Range, FindYear As Range
    For iRow = 2 To 3
        year = Cells(iRow, 1)
        avg = Cells(iRow, 2)
        factor = Cells(iRow, 3)
        group = Cells(iRow, 4)
        Set FindGroup = Range("F1:CTZ1").Find(group, LookIn:=xlValues, Lookat:=xlWhole)
    
        Set FindUIC = Range(FindGroup, FindGroup.Offset(2812, 0)).Find(factor, LookIn:=xlValues, Lookat:=xlWhole)
        Set FindYear = Range(FindGroup, FindGroup.Offset(0, 7)).Find(year, LookIn:=xlValues, Lookat:=xlWhole)
        Debug.Print Cells(FindUIC, FindYear).Address
    Next iRow
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #13
    Board Regular
    Join Date
    Jun 2005
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with Find Method

    No, it's not even close. Your code gives me address: $BYK$2, which is something else entirely, and the correct address is $CI$3

    If I add the code:

    Code:
        uic = FindUIC.Address
        fy = FindYear.Address
        Debug.Print uic, fy
    I get the correct cells references: $CH$3 $CI$1
    Last edited by ouadad; Oct 1st, 2019 at 03:10 PM.

  4. #14
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need help with Find Method

    Quote Originally Posted by ouadad View Post
    No, it's not even close. Your code gives me address: $BYK$2, which is something else entirely, and the correct address is $CI$3
    If that is the case how did you manage to get
    It just outputs the address for FindGroup into the correct cell.
    in post number 9?
    Asking because the code I asked you to test is the same code as you had in post number 9 just with
    Code:
    Cells(FindUIC, FindYear)= avg
    changed to
    Code:
    Debug.Print Cells(FindUIC, FindYear).Address
    and the loop restricted.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #15
    Board Regular
    Join Date
    Jun 2005
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with Find Method

    It's quire possible that the printing of $CI$3 was done through a previous iteration of the code without me noticing it. There are a number of changes I am making as we go to see what works and what doesn't. I don't mean to frustrate you.

  6. #16
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need help with Find Method

    I don't mean to frustrate you
    I'm not frustrated, I am just puzzled how the ranges FindUIC and FindYear can be used to get the correct results in the Cells syntax as I am not seeing what the relationship is.
    If it was the row number from one and the column number from the other I could understand it (or the values from the 2 ranges if they are both numbers).
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #17
    Board Regular
    Join Date
    Jun 2005
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with Find Method

    Quote Originally Posted by MARK858 View Post
    I'm not frustrated, I am just puzzled how the ranges FindUIC and FindYear can be used to get the correct results in the Cells syntax as I am not seeing what the relationship is.
    If it was the row number from one and the column number from the other I could understand it (or the values from the 2 ranges if they are both numbers).
    I just tried it again and got no response, so I'm not sure what happened before

    Your code chose $BYK$2 because the FindUIC = 0002 and FindYear=2013 and row 2 and column 2013 is BYK2
    Last edited by ouadad; Oct 1st, 2019 at 03:54 PM.

  8. #18
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need help with Find Method

    Ok, now I know what the previous code was giving, now the question is how do we get the range that you want from the 2 variables? I am still not sure how you are trying to use them to get a range.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #19
    Board Regular
    Join Date
    Jun 2005
    Posts
    209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need help with Find Method

    I found a solution. Here's my code. This works. Thank you for all your help moving this forward.

    Code:
    Sub Friedman_Test()
    Dim year As Integer, avg As Double, factor As String, group As Integer, iRow As Long
    Dim FindUIC As Range, FindGroup As Range, FindYear As Range, uic As Variant, fy As Variant
    Dim rnum As Integer, cnum As Integer
    For iRow = 2 To 4
        year = Cells(iRow, 1)
        avg = Cells(iRow, 2)
        factor = Cells(iRow, 3)
        group = Cells(iRow, 4)
        Set FindGroup = Range("F1:CTZ1").Find(group, LookIn:=xlValues, Lookat:=xlWhole)
    '    Debug.Print FindGroup.Address
        Set FindUIC = Range(FindGroup, FindGroup.Offset(2812, 0)).Find(factor, LookIn:=xlValues, Lookat:=xlWhole)
        Set FindYear = Range(FindGroup, FindGroup.Offset(0, 7)).Find(year, LookIn:=xlValues, Lookat:=xlWhole)
        rnum = Range(FindUIC.Address).Row
        cnum = Range(FindYear.Address).Column
        Debug.Print rnum, cnum
        Cells(rnum, cnum) = avg
    Next iRow
    End Sub

  10. #20
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Need help with Find Method

    You shouldn't need Address or Range
    Code:
        rnum = FindUIC.Row
        cnum = FindYear.Column
    should do (glad it at least makes sense now as it was what I speculated on in post number 16).

    Or you could just do
    Code:
        Set FindUIC = Range(FindGroup, FindGroup.Offset(2812, 0)).Find(factor, LookIn:=xlValues, Lookat:=xlWhole)
        Set FindYear = Range(FindGroup, FindGroup.Offset(0, 7)).Find(year, LookIn:=xlValues, Lookat:=xlWhole)
    
        Cells(FindUIC.Row, FindYear.Column) = avg
    Happy that you got it sorted
    Last edited by MARK858; Oct 1st, 2019 at 04:14 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

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
  •