VBA Cell Referencing
 

Hi,
I have an issue with cell referencing.

The cells I need to reference are currently in columns J:J and K:K, (J
and K3).

I reference these cells from (S3) and have a basic formula in place t
populate (S3).

The issue is that the cells I reference can change every week from (J
and K3) or to (H3 and I3), for example. Also the cell that I populat
using data from the two cells can also change from (S3) to (P3), fo
example.

Once I establish the first two cells I need to reference I then ( a
the moment) copy and past the formula to populate the records belo
(S3), which uses data from cells below (J3 and K3). All this is done i
VBA using R1C1, which is great when the cell values are fixed.

What I can't work out is how to reference the two variable positio
cells that I will be having. I am thinking of a loop, but withou
having reference to the variable cell positions, I am not sure how t
proceed.

One thing that may help is that there is a heading above both the cell
that I reference that does not change.

Any ideas out there

--
Fastbik
-----------------------------------------------------------------------
Fastbike's Profile: http://www.excelforum.com/member.php...fo&userid=2670
View this thread: http://www.excelforum.com/showthread.php?threadid=50814

VBA Cell Referencing
 

Dim res1 as Variant, res2 as Variant
Dim rng1 as Range, rng2 as Range
res1 = Application.Match("Header1",Rows(1),0)
res2 = Application.Match("Header2",Rows(1),0)

if iserror(res1) or iserror(res2) then
Msgbox "Bad Data, getting out"
exit sub
end if
set rng1 = Range("A3")(1,res1)
set rng2 = Range("A3")(1,res2)

--
Regards,
Tom Ogilvy

"Fastbike" wrote in
message ...

Hi,
I have an issue with cell referencing.

The cells I need to reference are currently in columns J:J and K:K, (J3
and K3).

I reference these cells from (S3) and have a basic formula in place to
populate (S3).

The issue is that the cells I reference can change every week from (J3
and K3) or to (H3 and I3), for example. Also the cell that I populate
using data from the two cells can also change from (S3) to (P3), for
example.

Once I establish the first two cells I need to reference I then ( at
the moment) copy and past the formula to populate the records below
(S3), which uses data from cells below (J3 and K3). All this is done in
VBA using R1C1, which is great when the cell values are fixed.

What I can't work out is how to reference the two variable position
cells that I will be having. I am thinking of a loop, but without
having reference to the variable cell positions, I am not sure how to
proceed.

One thing that may help is that there is a heading above both the cells
that I reference that does not change.

Any ideas out there?


--
Fastbike
------------------------------------------------------------------------
Fastbike's Profile:
http://www.excelforum.com/member.php...o&userid=26701
View this thread: http://www.excelforum.com/showthread...hreadid=508144

VBA Cell Referencing
 

Hi Tom,
thanks for the info, it works a treat.

Your help is greatly appreciated.

Thanks again.


--
Fastbike
------------------------------------------------------------------------
Fastbike's Profile: http://www.excelforum.com/member.php...o&userid=26701
View this thread: http://www.excelforum.com/showthread...hreadid=508144