Range parameter on different sheet not working?

[Steve Newhouse]

Hello all,

I'm having trouble with a function that takes a range as a parameter.
I'm using the function on one sheet, but passing in a range that is on
another sheet called "Data", so something like Foo(Data!A1:A10). The
formula seems to ignore the sheet reference and use A1:A10 on the page
from which the function is called... How can I make sure that the range
that is iterated through is on the correct sheet, not just the sheet
that calls the function?

function Foo(InputRange As Range)
Dim cl As Range

For Each cl In InputRange
'do something
' PROBLEM IS HERE: even if I pass "Data!A1:A10", the cl range
is on the current
' sheet, not the Data sheet.
Next cl
End Function

I tried something like this, but it didn't work:

Dim DataSheet As Worksheet
DataSheet = ActiveWorkbook.Sheets("Data")
Set cl = DataSheet.Range(cl)

You can see what I'm after though.

Thanks for your help...

Steve

 

[Guest]

Try to select the right sheet prior to processing the cells:

Function foo(InputRange As Range) As Variant
Dim s As String
s = InputRange.Worksheet.Name
Sheets(s).Select

 

[Andrew Taylor]

I'm not sure how you're passing InputRange to Foo: you say
you do "something like Foo(Data!A1:A10)", but that isn't
valid VBA, and Foo("Data!A1:A10") gives a Type Mismatch.

The following works for me

Sub Main
Dim myRange as Range
Set myRange = ActiveWorkbook.Worksheets("Data").Range("A1:A10")
Foo myRange ' parens not needed when calling a Sub
End Sub