Active cell, dynamic input using Index Match in VBA

Question

I am trying to smarten up the attached excel spreadsheet using vba (sorry image attached). The sheet has a fixed value in cell D161, set at 45, Index match uses this value to return a summary of the values in the critical columns. I have attached a couple of modules with the sheet module one searches and finds and active cell based upon the input value of D161. I have seen a couple of examples and have become more confused than usual My work flow is:

select and active cell value, based upon D161’s value
Use this cell value as the active cell
Select the critical columns (highlighted in lt blue) based upon this active cell (using offset) There are 5 columns distributed through the array of values
Select the columns and present them in a summary table as shown in array D161:K167

I have found a topic that is similar however I’m moderately puzzled as its beyond my vba experience

Excel / VBA - Index Match function using Dynamic Ranges

My Code

Sub FindThisValue()

Dim varLookFor As Variant
Dim GBAnchorageRng As Range
Dim GBLapRng As Range
Dim OCAnchorageRng As Range
Dim OCLapRng As Range

varLookFor = Range("D161").Value

'Good bond cell ranges or columns
Set GBAnchorageRng = Range(ActiveCell, ActiveCell.Offset(6, 15))
Set GBAnchorageRng = Range(ActiveCell, ActiveCell.Offset(6, 15))

'Othercases (formly known as poor do not use this description bond) cell range or columns
Set OCAnchorageRng = Range(ActiveCell, ActiveCell.Offset(6, 26))
Set OCAnchorageRng = Range(ActiveCell, ActiveCell.Offset(6, 28))

On Error GoTo Handler:

'ActiveWorkbook.ActiveSheet.
Range("B93:B148").Find(What:=varLookFor, LookAt:=x1Whole = 1).Activate

'Specify range
'Range(ActiveCell.Offset(6, 0), ActiveCell.Offset(0, 31)).Select

Exit Sub

Handler:

MsgBox ("Value not found")

End Sub

user3598756 · Accepted Answer · 2016-12-16T14:45:13.640

0

you could try this

Sub FindThisValue()
    Dim f As Range

    Set f = Range("B93:B148").Find(What:=Range("D161").Value, LookAt:=xlWhole, LookIn:=xlValues)
    If f Is Nothing Then
        MsgBox ("Value not found")
        Exit Sub
    End If

    With Range("F161:F167")
        .Value = f.Offset(, 15).Resize(6).Value
        .Offset(, 2).Value = f.Offset(, 16).Resize(6).Value
        .Offset(, 3).Value = f.Offset(, 26).Resize(6).Value
        .Offset(, 5).Value = f.Offset(, 27).Resize(6).Value
    End With
End Sub

edited Dec 16 '16 at 14:45

answered Dec 16 '16 at 14:25

user3598756

28,893
4
18
28

f becomes the active cell, got it `.Resize(6).Value` helped show the path in the array `Resize(7).Value` captured all of the data required. Neat and makes sense. – Kenyon Graham Dec 18 '16 at 11:40
just to point out that my code doesn't rely on any properly called `ActiveCell` while it _reference_ `Range` objects. Which is much safer! – user3598756 Dec 18 '16 at 11:50

Active cell, dynamic input using Index Match in VBA

1 Answers1