finding the row value of a defined name
 

I need to set a constant (say Truck, dimensioned as an integer) to the value
of the row number of a cell identified to the macro by a defined name (say
Fleet). How do I do that please?

finding the row value of a defined name
 

You won't be able to set a constant based on the rownumber of a named
range as this may change - which means it can't be constant.

You could set it as a variable:

Dim Truck As Integer
Truck = Range("Fleet").Row

Hope this helps
Rowan

gvm wrote:
I need to set a constant (say Truck, dimensioned as an integer) to the value
of the row number of a cell identified to the macro by a defined name (say
Fleet). How do I do that please?

finding the row value of a defined name
 

Thanks Rowan, it works of course.

One final question (for this project anyway), I am using a range term, eg
Range("H14:N18"), where all four row and column references are variables, eg
row1, col1, row2, col2. How do I write these variables into the argument of
Range() please?

finding the row value of a defined name
 

Did you mean something like this:

Dim row1 As Long
Dim col1 As Integer
row1 = Range("Fleet").Cells(1, 1).Row
col1 = Range("Fleet").Cells(1, 2).Column
'etc

Regards
Rowan

gvm wrote:
Thanks Rowan, it works of course.

One final question (for this project anyway), I am using a range term, eg
Range("H14:N18"), where all four row and column references are variables, eg
row1, col1, row2, col2. How do I write these variables into the argument of
Range() please?

finding the row value of a defined name
 

Dim r1 as long
dim c1 as long
dim r2 as long
dim c2 as long
dim myRng as range

with worksheets("sheet1")
set myrng = .range(.cells(r1,c1),.cells(r2,c2))
end with

(I'd use Long instead of integer.)


gvm wrote:

Thanks Rowan, it works of course.

One final question (for this project anyway), I am using a range term, eg
Range("H14:N18"), where all four row and column references are variables, eg
row1, col1, row2, col2. How do I write these variables into the argument of
Range() please?

--

Dave Peterson

finding the row value of a defined name
 

Thanks Rowan and Dave,
Dave's response is close to what need (sorry Rowan I was not clearer in my
question) but I'm not quite there yet. I used Dave's information in the
following way:

Range(.Cells(row1, col1), .Cells(row2, col1 +6)).Select
Selection.ClearContents

and I get a compile error. The term ".cells" is highlighted and the error
message is "invalid or unqualified reference"

Thanks again in anticipation .. Greg

finding the row value of a defined name
 

I had

with worksheets("sheet1")
set myrng = .range(.cells(r1,c1),.cells(r2,c2))
end with

That with/end with is important. It means the things that are prefixed with
dots belong to the previous With statement--in my example, Sheet1.

So you could use:

Range(Cells(row1, col1), Cells(row2, col1 +6)).ClearContents

or include that worksheet to make sure you get the correct sheet.

with worksheets("sheet1")
.Range(.Cells(row1, col1), .Cells(row2, col1 +6)).ClearContents
end with

(No need to select in either case.)

gvm wrote:

Thanks Rowan and Dave,
Dave's response is close to what need (sorry Rowan I was not clearer in my
question) but I'm not quite there yet. I used Dave's information in the
following way:

Range(.Cells(row1, col1), .Cells(row2, col1 +6)).Select
Selection.ClearContents

and I get a compile error. The term ".cells" is highlighted and the error
message is "invalid or unqualified reference"

Thanks again in anticipation .. Greg

--

Dave Peterson

finding the row value of a defined name
 

Whoops careless of me, thanks Dave. The support delivered through this forum
is excellent,
regards
Greg