Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Need help with Find Method

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

    Default Need help with Find Method

    I have four columns with over 400,000 rows of data labelled (Year, Avg, UIC, Group) that look like (2013, 1.563, 0011, 23). I want to distribute them into 322 separate matrices, one for each Group that have columns labelled by the year: 2013 to 2019 and rows labelled by the UIC from 0001 to 9999 (2681 UIC values). I tried using a pivot table, which worked to some extent, but it left me with the problem of identifying and filling in UIC and Year values which the pivot table ignores because they're empty (I need all Group, UIC and Year cells to be filled in by either an Avg, or left blank).

    I wrote some code using the Find method (see below), but it gives me a run time 424 "Object Required" error for the line:Set FindUIC ... Could someone give me some guidance please?

    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
    For iRow = 2 To 440052
        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
        cell = FindGroup.Address
        Set FindUIC = Range(cell, cell.Offset(2812, 0)).Find(factor, LookIn:=xlValues, Lookat:=xlWhole)
        Set FindYear = Range(cell, cell.Offset(0, 7)).Find(year, LookIn:=xlValues, Lookat:=xlWhole)
        Cells(FindUIC.Address, FindYear.Address) = avg
    Next iRow
    End Sub
    Last edited by ouadad; Oct 1st, 2019 at 11:58 AM.

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

    Default Re: Need help with Find Method

    Untested but what happens if you change
    Code:
    cell = FindGroup.Address
    to
    Code:
    cell = FindGroup
    Btw I think it is risky using cell as a variable name

    I expect that you are going to get issues when you reach the line below as well

    Code:
    Cells(FindUIC.Address, FindYear.Address) = avg
    as you can't use .Address as part of the Cells syntax.
    Last edited by MARK858; Oct 1st, 2019 at 12:01 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

  3. #3
    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 just gives the value that I read in though the for loop. I need the cell address to find the location and move through the columns for the Year and the rows for the UIC.
    Last edited by ouadad; Oct 1st, 2019 at 12:02 PM.

  4. #4
    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

    Here's an example of one of the 322 matrices, this one for Group 7.

    [img]
    Excel 2010
    BBBCBDBEBFBGBHBI
    172013201420152016201720182019
    20001
    30002
    40004
    50005
    60006
    70008

    uic_FTest



    Worksheet Formulas
    CellFormula
    BB1=AT1+1


    [/img]

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

    Default Re: Need help with Find Method

    Code:
    Set FindUIC = Range(FindGroup, FindGroup.Offset(2812, 0)).Find(factor, LookIn:=xlValues, Lookat:=xlWhole)
    You don't want Address as Address is a string not a range and so you can't offset from it.
    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

  6. #6
    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

    But FindGroup just takes the value of Group that is read in from the For Loop. In the example at the top that would be just Group=23, which is not a range value. I need the address where Group=23 resides and then use FindUIC and FindYear to move through the rows and the columns for Group=23, in the example.
    Last edited by ouadad; Oct 1st, 2019 at 12:31 PM.

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

    Default Re: Need help with Find Method

    FindGroup is a RANGE not a value, you have defined it as such and you wouldn't be able to use Set if it wasn't.
    Last edited by MARK858; Oct 1st, 2019 at 12:40 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

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

    Default Re: Need help with Find Method

    Just as a basic example copy the data below to a blank worksheet, run the code and see what is selected.

    Excel 2016 (Windows) 64 bit
    ABCDEFG
    13567613598196628248
    270990095245989983209
    312457717292122448676
    48837892916626515748
    512647990294487981291
    668792783747451588076
    7976680377405170982887
    8435152247867106600771
    94988425663519193234
    1080193717434612293906
    11155676239224517796343
    12256439304436490654710
    13537473551778346510595
    1468253314442798736101
    15516511488545468309617
    1663511549408160166240
    1787416019354575234827
    18490191104838450683678
    19997544168703257676954
    202406244139083363583

    Sheet5






    Code:
    Sub ffff()
        Dim group As Integer, FindUIC As Range, FindGroup As Range
    
        group = 566
        Set FindGroup = Range("A1:G20").Find(group, LookIn:=xlValues, Lookat:=xlWhole)
    
        Set FindUIC = Range(FindGroup, FindGroup.Offset(10, 0))
        FindUIC.Select
    
    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

  9. #9
    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 apologize. You are correct, of course. That worked, but now the last line doesn't seem to work.

    Code:
    Cells(FindUIC, FindYear) = avg

    It just outputs the address for FindGroup into the correct cell. My new code is

    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
    For iRow = 2 To 440052
        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)
        Cells(FindUIC, FindYear) = avg
    Next iRow
    End Sub

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

    Default Re: Need help with Find Method

    That is as I stated in post number 2

    I expect that you are going to get issues when you reach the line below as well
    Code:
    Cells(FindUIC.Address, FindYear.Address) = avg
    as you can't use .Address as part of the Cells syntax.
    The syntax for cells is
    Cells(Row number, Column number or Column letter (in quotes))
    Explain in words where you want the avg placed if you can't get there with the above syntax.
    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
  •