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

 

[MARK858]

Untested but what happens if you change

cell = FindGroup.Address

to

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

Cells(FindUIC.Address, FindYear.Address) = avg

as you can't use .Address as part of the Cells syntax.

 

[ouadad]

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.

 

[ouadad]

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

 

[MARK858]

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.

 

[ouadad]

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.

 

[MARK858]

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.

 

[MARK858]

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
	A	B	C	D	E	F	G
1	35	676	135	981	96	628	248
2	709	900	952	45	989	983	209
3	124	577	172	921	22	448	676
4	88	378	929	166	26	515	748
5	126	47	990	294	487	981	291
6	687	927	837	474	515	880	76
7	976	680	377	405	170	982	887
8	435	152	247	867	106	600	771
9	498	842	566	351	919	32	34
10	80	193	717	434	612	293	906
11	155	676	239	224	517	796	343
12	256	439	304	436	490	654	710
13	537	473	551	778	346	510	595
14	682	533	144	42	798	736	101
15	516	511	488	545	468	309	617
16	635	115	49	408	160	166	240
17	874	160	19	354	575	234	827
18	490	191	104	838	450	683	678
19	997	544	168	703	257	676	954
20	240	624	413	90	83	363	583
Sheet5

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

 

[ouadad]

I apologize. You are correct, of course. That worked, but now the last line doesn't seem to work.

Cells(FindUIC, FindYear) = avg

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

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

 

[MARK858]

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

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.