Tom,

Many thanks - apologies I am so dumb I did not even think of whether it was
a sheet module or not - now I see but no just a function on a worksheet.

I thought before I tried both versions and they didn't work but the non-form
one works perfectly now so thank you very much and my apologies - I must have
gone wrong somewhere !

Actually I did come across my own way of getting round the problem(though
yours is obviously more efficient) - be interested to see what you think of
the following:


Public Function regionTotal(countryRng As String, msgOption As String) As Long

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim myRange As Range
Dim myCel As Range


Dim Total As Long

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

Dim myStr As String
Dim myformula As String

Set myCel = Range("b3")
myformula = myCel.Formula
Set rng = Range(myformula)

Total = 0
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)

'returns as "calc!H35"
myStr = "calc!H" & returnFormattedStringFromNumber(rng2.Row)

'sets rng5.value as "query!M8:M23"
Set rng5 = Range(myStr)
Set rng6 = Range(rng5)

Total = Application.WorksheetFunction.Sum(rng6)
End If

regionTotal = Total

End Function

Public Function returnFormattedStringFromNumber(inputNumber As Integer) As
String

Dim myStr As String

myStr = Str(inputNumber)
myStr = Right(myStr, Len(myStr) - 1)

returnFormattedStringFromNumber = myStr

End Function




"Tom Ogilvy" wrote:

If your not doing this in a sheet module then you can do

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

like I said in my previous response.

If it is in a sheet module then do

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


But this limits the flexibility of this approach.

Yes, what you said and which is what i previously said, is the problem.

--
Regards,
Tom Ogilvy


"Jello" wrote in message
...
Dear Tom,

Thanks for the reply.
I think this is probably my fault again in that I wonder if it is because
the reference to column "H" is on another sheet named calc, the same sheet
where we got the search range info via Set rng = Range("calc!B10") '
Apples
Set rng1 = Range("calc!B33:B65") ' range to be searched

So I have:
rng2 is B36 value but on sheet 'calc' and = "Michael Adams UK"
H36 contains the string "query!M2:M13" without the double quotes
BUT AGAIN THIS CELL IS ON SHEET 'calc' !

Is that why it fails when you say:
Total = Application.WorksheetFunction.Sum(rng2.parent.Rang e( _
rng2.parent.Cells(rng2.Row, "H").Value).Value)

as it is expecting column H from the active sheet ? I assigned a macro to
my
function "regionTotal" in a cell on another sheet !
If this is the case is there any way round this ?
I am currently experimenting with how to set ranges....

Apologies if that was crucial.
Many thanks,
Jello


"Tom Ogilvy" wrote:

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