I am trying to use lookup function with VBA for a dynamic range. The figure below is an example of few lines of data that I am trying to test with. The top two rows are my header rows that represent range of ‘heights’ for each ‘year’ of available data.
The data in black font is my row data. From where with code I am getting the red font data which are just the max values (i.e. the largest values) for each height for all available years for that height. Now I am also trying to find the year of the max value with the lookup function in VBA and paste to the right hand cells as shown in blue font in Figure 1. I can do this for a selected range using the following code and produce the output as shown in Figure 1 but I could not logically think of how to do this for a dynamic range.
My problem is that my row data that is highlighted in black is dynamic range and data highlighted in red also will be of a dynamic range depending of number of ‘heights’. So I am struggling to think of a logical way of setting the two ranges of black and red texts and find the year of the maximum value as shown in blue colour in figure 1. I would be greatful if someone could give me some advise on how I could approach to this problem. Thanks in advance!
Sub Lookup()
Range("K3").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(RC[-3],RC[-10]:RC[-4],R[-1]C[-10]:R[-1]C[-4])"
Range("K3").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-3],RC1:RC7,R2C1:R2C7)"
Range("K3").Select
Selection.AutoFill Destination:=Range("K3:M3"), Type:=xlFillDefault
Range("K3:M3").Select
Selection.AutoFill Destination:=Range("K3:M5"), Type:=xlFillDefault
Range("K3:M5").Select
End Sub
This isn't exactly what you have going, but since I already worked it up and Tested it. Working, it delivers the results you wanted and allows for more years to be input later.
Private Sub FilterMax()
Dim max10 As Single
Dim max20 As Single
Dim max30 As Single
Dim max10Year As Long
Dim max20Year As Long
Dim max30Year As Long
Dim row As Long
Dim lastRow As Long
Dim firstYear As Long
Dim lastYear As Long
Dim year As Long
Dim sheet As String
lastRow = Sheets("MaxValues").Range("A" & Rows.Count).End(xlUp).row
'You might want to put an input box up or just manually set this.
firstYear = 2012
lastYear = 2014
For row = 2 To lastRow
'reset max for each DataRow
max10 = 0
max10Year = 0
max20 = 0
max20Year = 0
max30 = 0
max30Year = 0
For year = firstYear To lastYear
sheet = CStr(year)
'Max10
If Sheets(sheet).Cells(row, 2) > max10 Then
max10 = Sheets(sheet).Cells(row, 2)
max10Year = Sheets(sheet).Range("G1")
End If
'Max20
If Sheets(sheet).Cells(row, 3) > max20 Then
max20 = Sheets(sheet).Cells(row, 3)
max20Year = Sheets(sheet).Range("G1")
End If
'Max30
If Sheets(sheet).Cells(row, 4) > max30 Then
max30 = Sheets(sheet).Cells(row, 4)
max30Year = Sheets(sheet).Range("G1")
End If
Next year
Sheets("MaxValues").Cells(row, 2).Value = max10
Sheets("MaxValues").Cells(row, 2).Font.Color = vbRed
Sheets("MaxValues").Cells(row, 3).Value = max10Year
Sheets("MaxValues").Cells(row, 3).Font.Color = vbBlue
Sheets("MaxValues").Cells(row, 4).Value = max20
Sheets("MaxValues").Cells(row, 4).Font.Color = vbRed
Sheets("MaxValues").Cells(row, 5).Value = max20Year
Sheets("MaxValues").Cells(row, 5).Font.Color = vbBlue
Sheets("MaxValues").Cells(row, 6).Value = max30
Sheets("MaxValues").Cells(row, 6).Font.Color = vbRed
Sheets("MaxValues").Cells(row, 7).Value = max30Year
Sheets("MaxValues").Cells(row, 7).Font.Color = vbBlue
Next row
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments