# Thread: Need help with Find Method Thanks:  3 Post #5349865 (1)Post #5349939 (1)Post #5349964 (1) Likes:  1 Post #5349964 (1)

1. ## 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)
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)
Next iRow
End Sub```  Reply With Quote

2. ## 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.  Reply With Quote

3. ## 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.  Reply With Quote

4. ## 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]  Reply With Quote

5. ## 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.  Reply With Quote

6. ## 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.  Reply With Quote

7. ## 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.  Reply With Quote

8. ## 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```  Reply With Quote

9. ## 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)
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```  Reply With Quote

10. ## 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.  Reply With Quote

## User Tag List

avg, cellsirow, group, uic, year 