VBA Index Match (error)

[ashish mehra]

Hi,

I am scratching my head all of the yesterday to find out the error in the code.

Can somebody check the attached file & let me know why my code is giving results when I select 1)Name as A in cell F7; 2) Subject as Eng in cell G7 & on the other hand I get no result when I change the Subject to Maths.

I tried using add watch with no luck.

Regards,
AM

 

[Nebu]

Hi:

The error is because the index formula should be an array formula in your vba it is not taken as array but as normal formula matching 1 row and 1 column, where as you need to match 2 columns to get the desired result, both the name and the subject.

Thanks

 

[Nebu]

Try this code

Sub vba2criteria()
myName = [F7]
mySubject = [G7]
m = ActiveSheet.Evaluate("=INDEX(C2:C12,MATCH(F7&G7,A2:A12&B2:B12,0))")
[H7] = m
End Sub

Thanks

 

[ashish mehra]

Thanks Nebu for your prompt reply.

Regards,
AM

 

[NARAYANK991]

Hi Ashish ,

The formula suggested by Nebu will always work , but in your present case , if you want to make use of the structure which is shown , where the marks are sorted Student-wise and for each Student , Subject-wise , with every student having all subjects , then use the following for m :

m = Application.WorksheetFunction.Index([StMark], Application.WorksheetFunction.Match(myName, ([StName]), 0) + Application.WorksheetFunction.Match(mySubject, [StSubject], 0) - 1)

Narayan

 

[ashish mehra]

Many thanks Narayan

I was looking for this one.

Regards,
AM

 

[Somendra Misra]

Hi Ashish,

Just another option try using this also in your code:

m = Application.WorksheetFunction.SumIfs([StMark], [StName], myname, [StSubject], mysubject)


Regards,

 

[ashish mehra]

Good Morning SM!

Thanks for having a look & providing another solution.

Thanks to Nebu, Narayan & SM

Regards,
AM