range(cells(...))-question

[sebastian1.schmidt]

Hi,
I want to address cells dynamically, i.e. have a search function loop
through certain columns. When I write something like
------------------
Function source_range(ByVal sheetname As String, ByVal deliveryperiod
As String) As Range
Dim localloop As Integer
'ThisWorkbook.Worksheets(sheetname).Activate
For localloop = 1 To max_delivery_periods
With ThisWorkbook.Worksheets(sheetname)
If .Cells(delivery_period_row, localloop).Value =
deliveryperiod Then
source_range = .Range(.Cells(1,
localloop), .Cells(max_timeseries_length, localloop)) 'error here!
Exit Function
End If
End With
Next localloop
End Function

('delivery_period_row is a public const of type integer, as well as
max_timeseriws_length)
-----------------------
I get an error, "objectvariable not defined" (or similar). Why is
this, and how can I assign the range "more clever"?

Thanks for any help!
Seb.

 

[Bob Phillips]

As far as I can see, delivery_period_row is never loaded, so it will be 0,
so the Cells will be invalid.

 

[sebastian1.schmidt]

As far as I can see, delivery_period_row is never loaded, so it will be 0,
so the Cells will be invalid.

--
__________________________________
HTH

Bob

Hi Bob,

sorry, I forgot to mention: delivery_period_row as well as
max_delivery_periods are public constants, and they are loaded
(integers with values nicely loaded).

Any other suggestion?

 

[Bob Phillips]

Function source_range(ByVal sheetname As String, ByVal deliveryperiod As
String) As Range
Dim localloop As Long

'ThisWorkbook.Worksheets(sheetname).Activate
For localloop = 1 To max_delivery_periods

With ThisWorkbook.Worksheets(sheetname)

If .Cells(delivery_period_row, localloop).Value = deliveryperiod
Then

Set source_range = .Range(.Cells(1, localloop),
..Cells(max_timeseries_length, localloop)) 'error here!
Exit Function
End If
End With
Next localloop
End Function