Need help with Find Method

[ouadad]

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?

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

 

[ouadad]

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.

 

[MARK858]

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

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

 

[ouadad]

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:

    uic = FindUIC.Address
    fy = FindYear.Address
    Debug.Print uic, fy

I get the correct cells references: $CH$3 $CI$1

 

[MARK858]

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

Cells(FindUIC, FindYear)= avg

changed to

Debug.Print Cells(FindUIC, FindYear).Address

and the loop restricted.

 

[ouadad]

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.

 

[MARK858]

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).

 

[ouadad]

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

 

[MARK858]

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.

 

[ouadad]

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

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

 

[MARK858]

You shouldn't need Address or Range

    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

    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