[SOLVED] How to use Index and match funtion in VBA

Issue

I would like to use the following function in VBA.

=INDEX(Zones!$G$3:$S$273;MATCH($A2;Zones!$C$3:$C$272;);MATCH($B2;Zones!$G$2:$S$2;))

I use two tables. In the table "Data" in column C2:C I would like to transfer the searched results.
Can anyone help with VBA coding?
Thanks!

Solution

without having tested it myself now, shooting from the hip, the VBA replacement should be trivial:

public function zone(DBase as Range, Locale as String, Locales as Range, Service as String, Services as Range) as Variant
  Dim iLocale as Long
  iLocale = WorksheetFunction.Match(Locale,Locales,0)
  Dim iService as Long
  iService = WorksheetFunction.Match(Service,Services,0)
  zone = DBase(iLocale,iService)
end function

Needs error checking, of course. I find your motivation still rather suspect 😉 Personally, I would not do it. Your ambition to import the data from elsewhere will most probably also not work as you intend.

Answered By – Ollie2893

Answer Checked By – David Goodson (BugsFixing Volunteer)