if rng2 is B36

and H36 contains the string "query!M2:M13" without the double quotes, then

Total = Application.WorksheetFunction.Sum(rng2.parent.Rang e( _
rng2.parent.Cells(rng2.Row, "H").Value).Value)

would evaluate to

Total =
Application.WorksheetFunction.Sum(rng2.parentRange ("query!M2:M13").Value)

If rng2 is in sheet query (which it appears it isn't), then that should
work. If it isn't, then you need to change the code to

Total = Application.WorksheetFunction.Sum(Range( _
rng2.parent.Cells(rng2.Row, "H").Value).Value)

Cell H36 didn't contain a sheet name when this was written.

--
Regards,
Tom Ogilvy



"Jello" wrote in message
...
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