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

 

[Domenic]

Any reason why you wouldn't simply use...

=INDEX(Range_numb_disposed,MATCH("X",Range_acq.schd_months,0),MATCH("Y",Range_asset_name,0))

???

 

[Steven39]

Hi Domenic,

Thank you for getting back to me, it's very much appreciated.
I have tried your suggestion and I get a message stating; compile error 'Sub or Function nor defined'.
It also highlights the 'Match' term in blue.

Function Test(Month, Asset_type)
Test = Index(Range_numb_disposed, Match(Month, Range_acq.schd_months, 0), Match(Asset_type, Range_asset_name, 0))
End Function

 

[Kamolga]

Hi,


As you did not dim Month and Asset_type (here it looks like string), I was not sure how you would enter your formula. I don't use Month as this is a function already and would lead to error.

1. Let's say you want to enter your formula selecting two cells, the first one being the month (A3 let's say), the second an asset type (C1), then this function

Function IndxM(rMonth As Range, Asset_Type As Range)
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(Asset_Type.Value).Column
    End With


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

should work for you. It would for example look like

=IndxM(A3,C1)

in a cell.

2. If instead you wish to name A3 nmonth (I avoid "month") and C1 Asset_type, you should use

Function IndxMa()Dim r As Long 'row
Dim c As Long 'column


With Range("Range_acq.schd_months")
 r = .Find(Range("nmonth").Value).Row
End With


With Range("Range_asset_name")
 c = .Find(Range("Asset_Type").Value).Column
End With
IndxMa = Range("Range_numb_disposed")(r, c).Value
End Function

and the same result would be obtained using

=IndxMa()

 

[Fluff]

I have tried your suggestion and I get a message stating; compile error 'Sub or Function nor defined'.

What Domenic was suggesting, was to just use the formula in the sheet rather than trying to convert it into a UDF.

 

[Steven39]

Hi Kamolga,

I very much appreciate your detailed response.

I tried both your suggestions and I no longer get a compile error and instead a '#Value' error.

I like your first suggestion I like so will try and fix that one.

Maybe more information would help.

The r variable entered will be a number between '1 and 200' the c variable entered will be text eg "studio" or "1 bedroom"

Any help you can give to get this to work would be amaizng.

 

[Steven39]

Maybe I need to dim c differently as its text and numbers?

 

[Steven39]

Thanks I was not on the ball there. I have it working in the sheet, but need it in VBA as there is 6 or 7 of these I need to put into one cell and it get very long the formulas.
Any help would be appreciated.

What Domenic was suggesting, was to just use the formula in the sheet rather than trying to convert it into a UDF.

 

[Kamolga]

You don't need to adapt r or c, this is calculation number that find the row and column base on the value of the cell.

 

[Steven39]

You don't need to adapt r or c, this is calculation number that find the row and column base on the value of the cell.

Hi,

Apologies if I am missing something obvious here, but still can get it to work.

The last part of your code which I have put in gree works perfectly if set r = 110 ad c = 1 for example and pulls the info I need from row 110 and column 1 in "Range_numb_disposed"

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

* 1: the value 80 (which is A1, set as rMonth in the script) is not being found in 'Range_acq.schd_months' for some reason and not returning its row number it is in in the range.
* 2: the value "studio" (set as Asset_Type in the script) is not being found in 'Range_asset_name' for some reason and not returning its row number which it is in in the range.



Function IndxM(rMonth As Range, Asset_Type As Range)
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(Asset_Type.Value).Column
End With




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

Hope this makes sense and thanks again