How to specify the sheet a worksheet function should use via a range object?

Question

I have some code that searches a column of values in sheet(3) in the format of a string "value1 - value 2"

value2 is the first value in a column in sheet(2) and value1 is a value in the same column, in a cell further down the sheet.

The setup I have is:

In sheet(1), cells C2:C6 have values a-e respectively

In sheet(2), cell C1 has value "yes" and cells C2:C6 have values 1-5 respectively

In sheet(3), cell A2 has the value "4 - yes"

So the code should countif a column in sheet2 with the first value being yes and look for cells with the value 4, and put the result in cell B2 on sheet(3)

What it actually does is find the yes column (column C) and search the same column on sheet(1) (so the message boxes show letters rather than numbers).

Is there a way I can more precisely specify the sheet the countif function uses?

I'm using Excel 2000 on Windows 7

Private Sub test_click()

scenario_count = 6
Dim i As Integer
i = 1

Sheets(2).Select
For j = 2 To 24
    If Sheets(2).Cells(1, j).Value = Right(Sheets(3).Cells(i + 1, 1).Value, Len(Sheets(3).Cells(i + 1, 1).Value) - InStrRev(Sheets(3).Cells(i + 1, 1).Value, "-") - 1) Then
        MsgBox ("number of scenarios is " & scenario_count)
        MsgBox ("value searching for is " & "'" & Left(Sheets(3).Cells(i + 1, 1).Value, InStrRev(Sheets(3).Cells(i + 1, 1).Value, "-") - 2) & "'")
        MsgBox ("Range searched is " & Range(Cells(2, j), Cells(scenario_count, j)).Address & " in " & ActiveSheet.Name)
        MsgBox ("Number of occurrences " & Sheets(2).Application.WorksheetFunction.CountIf(Range(Cells(2, j), Cells(scenario_count, j)), Left(Sheets(3).Cells(i + 1, 1).Value, InStrRev(Sheets(3).Cells(i + 1, 1).Value, "-") - 2)))

        Sheets(2).Select
        Sheets(3).Cells(i + 1, 2).Value = Sheets(2).Application.WorksheetFunction.CountIf(Range(Cells(2, j), Cells(scenario_count, j)), Left(Sheets(3).Cells(i + 1, 1).Value, InStrRev(Sheets(3).Cells(i + 1, 1).Value, "-") - 2))

        For Each c In Range(Cells(2, j), Cells(scenario_count, j))
            MsgBox ("comparing " & c.Address & " " & c.Value & " with " & Left(Sheets(3).Cells(i + 1, 1).Value, InStrRev(Sheets(3).Cells(i + 1, 1).Value, "-") - 2))
        Next c

    GoTo endofif2

    End If
Next
endofif2:
End Sub

The first argument to the Countif function should be: `Sheets(2).Range(Sheets(2).Cells(2, j), Sheets(2).Cells(scenario_count, j))` — Rory, Jul 22 '15 at 15:51
To add to what @Rory said, if you don't specify a sheet, it will assume Activesheet — puzzlepiece87, Jul 22 '15 at 15:53

Grade 'Eh' Bacon · Accepted Answer · 2015-07-22T15:55:59.603

0

Where you have 'WorksheetFunction.CountIf(Range(Cells(2, j)', simply insert the sheet before the range reference, like so:

Sheets(2).Range(Sheets(2).Cells(2, j), Sheets(2).Cells(scenario_count, j))

EDIT full formula which references the sheet for both the Cells and the Range functions blatently taken from @Rory's comment.

edited Jul 22 '15 at 15:55

answered Jul 22 '15 at 15:51

Grade 'Eh' Bacon

3,773
4
24
46

You need to qualify the `Cells` calls with the worksheet too! – Rory Jul 22 '15 at 15:52
@Rory Agreed - blatently just pointing to yours now with the full formula. – Grade 'Eh' Bacon Jul 22 '15 at 15:55
This worked, just needed to add "Sheets(2)." to everything that was in sheets(2) – A Francis Jul 27 '15 at 11:48

How to specify the sheet a worksheet function should use via a range object?

1 Answers1