Can't activate sheet in function

[lynxbci]

Hi,

I dont think that my question was clear enough in the earlier post. The function below worked on a single sheet, but then i added code to enable it to look at other worksheets in the workbook, and it stopped working.

The issue i think is with the line below, as it is not making nsht active!!

Sheets(nsht).Activate
R = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

This is the full function

Function Sum2if(rng1 As Range, crt1 As Variant, rng2 As Range, crt2 As Variant, tots As Range)

Dim R As Integer
Dim i As Integer

' **** Added parts ****

Dim osht As String
Dim nsht As String

osht = ActiveSheet.Name
nsht = rng1.Worksheet.Name

Sheets(nsht).Activate

'***** end of added parts *******
R = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

For i = 1 To R
    If rng1.Cells(i, 1) = crt1 And rng2.Cells(i, 1) = crt2 Then
                                        Sum2if = Sum2if + tots.Cells(i, 1)
    End If
Next i

' ****** added part *****
Sheets(osht).Activate
' ***** end of added part ****

End Function

Any suggestions please?

 

[GlennUK]

Is this a function that is referenced in a worksheet cell?

And why are you trying to activate sheets instead of referencing them directly?

 

[lynxbci]

Hi,

How do i reference them dircetly?

thanks

 

[GlennUK]

Remove your newly added parts, and change your R calculation to be:

R = rng1.Worksheet.UsedRange.Rows.Count + rng1.Worksheet.UsedRange.Row - 1

... at a guess.

 

[lynxbci]

Somtimes i think i should just stay in bed on Fridays........

Thanks for the help Glenn