lynxbci
Board Regular
- Joined
- Sep 22, 2004
- Messages
- 201
- Office Version
- 365
- Platform
- Windows
- MacOS
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!!
This is the full function
Any suggestions please?
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!!
Code:
Sheets(nsht).Activate
R = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1This is the full function
Code:
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 FunctionAny suggestions please?
