Need help with Find Method

ouadad

Board Regular
Joined
Jun 19, 2005
Messages
213
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:
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:
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 :biggrin:
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top