Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range(cells(...))-question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range(cells(...))-question
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 wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range(cells(...))-question
On Jul 28, 10:04 pm, "Bob Phillips" wrote:
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 wrote in message ... 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. 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range(cells(...))-question
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 -- __________________________________ HTH Bob wrote in message ... On Jul 28, 10:04 pm, "Bob Phillips" wrote: 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 wrote in message ... 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. 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about a range of cells | Excel Discussion (Misc queries) | |||
Range.(Cells()) question | Excel Programming | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Formula and Range question using Excel Automation | Excel Programming |