I'm not too familiar with the Find method as I've always used For loops, which take way too much time. What I'm trying to do is go down a list of over 400,000 values in four columns and then take three of those values to determine where in a matrix the fourth value should be placed. For example, here is one row of values:
Year Average UIC Group
2013 6.615384615 0002 11
1. I want to first search a horizontal list for a particular Group (there are 322 groups in the list). The list will looks like this
1 2013 2014 2015 2016 2017 2018 2019
where the first number is the Group followed by seven dates. There are an additional 321 lists like this in a horizontal range where the Groups go from 1 to 322. So using the above example, I want to be able to find Group 11.
2. Once I find the Group, I want to find the UIC 0002 (using the example) to search a vertical list of 2811 values directly under the Group label. The offset would be row 1 to 2811 and column = 0.
3. Finally, I want to search the date list to the right of the Group number to find the year, in this case 2013.
Once I find the Group, UIC and Year, I want to place the value Average in the cell with row UIC and column Year for the correct Group.
I written some code (see below), but I know it isn't correct and was hoping that someone could help me out.
Year Average UIC Group
2013 6.615384615 0002 11
1. I want to first search a horizontal list for a particular Group (there are 322 groups in the list). The list will looks like this
1 2013 2014 2015 2016 2017 2018 2019
where the first number is the Group followed by seven dates. There are an additional 321 lists like this in a horizontal range where the Groups go from 1 to 322. So using the above example, I want to be able to find Group 11.
2. Once I find the Group, I want to find the UIC 0002 (using the example) to search a vertical list of 2811 values directly under the Group label. The offset would be row 1 to 2811 and column = 0.
3. Finally, I want to search the date list to the right of the Group number to find the year, in this case 2013.
Once I find the Group, UIC and Year, I want to place the value Average in the cell with row UIC and column Year for the correct Group.
I written some code (see below), but I know it isn't correct and was hoping that someone could help me out.
Code:
Sub Data_for_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)
Set FindUIC.Offset(2811, 0) = factor
Set FindYear.Offset(0, 7) = year
Cells(r, c) = avg
Next iRow
End Sub
