Creating a VBA function for index and match with dynamic named ranges ... Urgent please help!!!

[Steven39]

Afternoon,

I am hoping someone can help me with this. I have lots of dynamic index and matches needing to be placed in one formula and it is getting very long and messy.

As a result, I need to create functions for each one of them to shorten the formulas. I am struggling to get the below example of a function to work though.

Can someone please help

Example function VBA code:

Function Test(Month, Asset_type)

Test = Application.WorksheetFunction.Index(Range("Range_numb_disposed"), _


Application.WorksheetFunction.Match(Month,(Range("Range_acq.schd_months"), 0), _


Application.WorksheetFunction.Match(Asset_type,(Range("Range_asset_name"), 0) – 1)


End Function

*simply using _in the above code to split the same code onto another line as gets very long.

Dynamic range code:

Range_numb_disposed = OFFSET('Disposition schedule'!$G$3,0,0,COUNTA('Disposition schedule'!$G:$G)-2,4)
Range_acq.schd_months = OFFSET('Acquisition schedule'!$A$3,0,0,COUNTA('Acquisition schedule'!$A:$A),1)
Range_asset_name = 'Acquisition schedule'!$B$2:$F$2

Thank you for the help in advance,
Steven

 

[Kamolga]

What doesn't seem to be working is when I put IndxM(A1,"Studio") in a cell:

You need to select a cell where studio is written

If you want to type studio, you need the function to be

Function IndxM(rMonth As Range, Asset_Type As [COLOR=#ff0000]String[/COLOR])
Dim r As Long 'row
Dim c As Long 'column


    With Range("Range_acq.schd_months")
     r = .Find(rMonth.Value).Row
    End With
    
    With Range("Range_asset_name")
     c = .Find([COLOR=#ff0000]Asset_Type[/COLOR]).Column
    End With


IndxM = Range("Range_numb_disposed")(r, c).Value 
[COLOR=#333333]End Function[/COLOR]

 

[Steven39]

Go it working, below is the code.

I'm guessing 0 is assigned in a range, but not for index look up, hence needing to -1 got the column in the range that is really zero?

Anyway works

Thanks Kamolga you saved me a lot of time and stress.


Function IndxM(rMonth As Long, Asset_Type As String)
Dim r As Long 'row
Dim c As Long 'column




With Range("Range_acq.schd_months")
r = .Find(rMonth).Row - 2
End With

With Range("Range_asset_name")
c = .Find(Asset_Type).Column - 1
End With




IndxM = Range("Range_numb_disposed")(r, c).Value
End Function

 

[Kamolga]

That means you have a difference in column and rows in Range("Range_numb_disposed")(r, c) and Range("Range_acq.schd_months") & Range("Range_asset_name")

In array you have a value 0 but range starts normally at 1.

Glad it works!