Question about cell adressing

[Matt]

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

 

[Gary Keramidas]

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

 

[Dave Peterson]

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


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

if you care...

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

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

 

[Matt]

Dave, Gary,

thanks for the help! Will test it soon

Matt