formula construction II
 

hi all,

apparently INDIRECT doesn't work with dynamically defined ranges

i have 2 options - i can either redefine the ranges every time i insert a
row in this worksheet (re-defining 24 ranges each time)

or

i could write a function that would take as arguments 2 strings, concatenate
them, and then return the correct range.

any thoughts on the practicality of option 2?

Public Function fixRange(loc As Range, app As String) As Range
fixRange = Range((loc & app)).Address
End Function

doesn't seem to work - although in the debugger the range is determined
correctly.

J

formula construction II
 

set fixRange = Range((loc & app))

works

J

"Gixxer_J_97" wrote:

hi all,

apparently INDIRECT doesn't work with dynamically defined ranges

i have 2 options - i can either redefine the ranges every time i insert a
row in this worksheet (re-defining 24 ranges each time)

or

i could write a function that would take as arguments 2 strings, concatenate
them, and then return the correct range.

any thoughts on the practicality of option 2?

Public Function fixRange(loc As Range, app As String) As Range
fixRange = Range((loc & app)).Address
End Function

doesn't seem to work - although in the debugger the range is determined
correctly.

J

formula construction II
 

Sounds like you answered your own question.

I don't think the address thing worked because you've stated the function
will return a range, but you are assigning a range address to your function,
which is a string.


"Gixxer_J_97" wrote:

hi all,

apparently INDIRECT doesn't work with dynamically defined ranges

i have 2 options - i can either redefine the ranges every time i insert a
row in this worksheet (re-defining 24 ranges each time)

or

i could write a function that would take as arguments 2 strings, concatenate
them, and then return the correct range.

any thoughts on the practicality of option 2?

Public Function fixRange(loc As Range, app As String) As Range
fixRange = Range((loc & app)).Address
End Function

doesn't seem to work - although in the debugger the range is determined
correctly.

J