Hi using matching to identify a particular cell, I want to search and sum the
range of the contents of a cell - in a different column for the same row.

e.g. I want to sum the range contained in cell H36 - e.g. "query!$M$2$M13$'
and I have already found the matching row by locating the single range cell
B36 which is set via Set rng2 = rng1(resFinal)

I set a particular cell as a range(rng2 is set to the value of B36) and then
try to refer to the same row but another column in the totals line

Can someone explain to me how ranges work e.g. what is Cells(rng2.Row,
"H").Value and why the totals line below is falling over
- I did not write it but when I run it the locals window blanks.

Many thanks again
Jello.


Public Function regionTotal(countryRng As String) As Long

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range

Dim Total As Long

Dim res1 As Variant
Dim res2 As Variant
Dim resFinal As Integer

Set rng = Range("calc!B10") ' Apples
Set rng1 = Range("calc!B33:B65") ' range to be searched
res1 = Application.Match("*" & rng & "*", rng1, 0)
res2 = Application.Match("*" & countryRng & "*", rng1, 0)

resFinal = Application.WorksheetFunction.Max(res1, res2)

If Not IsError(resFinal) Then
Set rng2 = rng1(resFinal)
Total = Application.WorksheetFunction.Sum(rng2.parent.Rang e( _
rng2.parent.Cells(rng2.Row, "H").Value).Value)
MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value
Else
MsgBox Range("calc!B10").Value & " was not found"

End If

regionTotal = Total

End Function