Folks,

I found a way to speed up my macro to select my data range by just
locating the first cell and then using count to count the used cells...

Now what I have is:
D
1 $D$17 (which is the start address)
2 623 (which is the amount of used cells)


So my data range is from $D$17 to $D$640.

Now I need to add 623 to the $D$17 in cell D2.

The formula would look like that Cell(D3)=($D$[a+b]).

Where a=17 (from $D$17)
b=623 (the value from D2!).


How does it need to be coded so that excel puts

$D$640 (doesnt need the $) in lets say, D3?

Thanks :)

Matt

see if this is what you want
=ROW(INDIRECT(D1))+D2

--


Gary


"Matt" wrote in message
oups.com...
Folks,

I found a way to speed up my macro to select my data range by just
locating the first cell and then using count to count the used cells...

Now what I have is:
D
1 $D$17 (which is the start address)
2 623 (which is the amount of used cells)


So my data range is from $D$17 to $D$640.

Now I need to add 623 to the $D$17 in cell D2.

The formula would look like that Cell(D3)=($D$[a+b]).

Where a=17 (from $D$17)
b=623 (the value from D2!).


How does it need to be coded so that excel puts

$D$640 (doesnt need the $) in lets say, D3?

Thanks :)

Matt

with activesheet
.range("d3").value _
= .range(.range("d1").value).offset(.range("d2").val ue).address(0,0)
end with


but I think you have a difference of 1 in your count. D17:D640 is really 624
cells.

if you care...

with activesheet
.range("d3").value _
= .range(.range("d1").value).offset(.range("d2").val ue-1).address(0,0)
end with

Matt wrote:

Folks,

I found a way to speed up my macro to select my data range by just
locating the first cell and then using count to count the used cells...

Now what I have is:
D
1 $D$17 (which is the start address)
2 623 (which is the amount of used cells)

So my data range is from $D$17 to $D$640.

Now I need to add 623 to the $D$17 in cell D2.

The formula would look like that Cell(D3)=($D$[a+b]).

Where a=17 (from $D$17)
b=623 (the value from D2!).

How does it need to be coded so that excel puts

$D$640 (doesnt need the $) in lets say, D3?

Thanks :)

Matt

--

Dave Peterson

Dave, Gary,

thanks for the help! Will test it soon :)

Matt