Cell references in excel vba code
 

I have a lot of code in the form;
Sheet4.Range("S" & iCount).Value = Range("B20").Value
Sheet4.Range("T" & iCount).Value = Range("C20").Value
Sheet4.Range("U" & iCount).Value = Range("D20").Value
Sheet4.Range("V" & iCount).Value = Range("E20").Value

works fine but I can't easily add columns to the sheet as all the
references are then wrong ! Bad design, I'm sure.

What can I do about this - can I reference columns differently ?

Any other ideas ?

Help would be most welcome.

Thanks

Cell references in excel vba code
 

Add defined names to the columns row 1 and use these

Cells(20, Range("nameCol").Value

for example

--

HTH

Bob

"Isis" wrote in message
...
I have a lot of code in the form;
Sheet4.Range("S" & iCount).Value = Range("B20").Value
Sheet4.Range("T" & iCount).Value = Range("C20").Value
Sheet4.Range("U" & iCount).Value = Range("D20").Value
Sheet4.Range("V" & iCount).Value = Range("E20").Value

works fine but I can't easily add columns to the sheet as all the
references are then wrong ! Bad design, I'm sure.

What can I do about this - can I reference columns differently ?

Any other ideas ?

Help would be most welcome.

Thanks

Cell references in excel vba code
 

Use named ranges for moving ranges.
http://www.ozgrid.com/Excel/named-ranges.htm

Or, if always the last used cell in Column;
http://www.ozgrid.com/VBA/ExcelRanges.htm


--
Regards
Dave Hawley
www.ozgrid.com
"Isis" wrote in message
...
I have a lot of code in the form;
Sheet4.Range("S" & iCount).Value = Range("B20").Value
Sheet4.Range("T" & iCount).Value = Range("C20").Value
Sheet4.Range("U" & iCount).Value = Range("D20").Value
Sheet4.Range("V" & iCount).Value = Range("E20").Value

works fine but I can't easily add columns to the sheet as all the
references are then wrong ! Bad design, I'm sure.

What can I do about this - can I reference columns differently ?

Any other ideas ?

Help would be most welcome.

Thanks

Cell references in excel vba code
 

"Bob Phillips" wrote in
:

Add defined names to the columns row 1 and use these

Cells(20, Range("nameCol").Value

for example


Bob and Dave, thanks very much for taking the time to reply.

Bob, I am not exactly understanding the code you gave me (it's me not you
!)

It is the alphabetic element I suppose I want to replace in some way as I
already have icount'ers for counting down rows.

I think your example does what I want - I just can't quite figure out how
to apply it.

In my example I am picking up info from a sheet that will not change -
the "Range("B20").Value" bit - I need to write it into another sheet
where I am tracking the row using my variable iCount - but would like to
change (when required) the Column - the "S" in "Sheet4.Range("S" &
iCount).Value".

Thanks again

Cell references in excel vba code
 

Okay, I just used the wrong side of the equation.

Assuming that column S (that is column S as of now) is a name column, add a
defined name of say nameCol and RefersTo either S1 or the whole column S:S,
and then use the Cells property not Range

Sheet4.Cells(iCount, Sheet4.Range("nameCol").Column).Value = ...

--

HTH

Bob

"Isis" wrote in message
...
"Bob Phillips" wrote in
:

Add defined names to the columns row 1 and use these

Cells(20, Range("nameCol").Value

for example


Bob and Dave, thanks very much for taking the time to reply.

Bob, I am not exactly understanding the code you gave me (it's me not you
!)

It is the alphabetic element I suppose I want to replace in some way as I
already have icount'ers for counting down rows.

I think your example does what I want - I just can't quite figure out how
to apply it.

In my example I am picking up info from a sheet that will not change -
the "Range("B20").Value" bit - I need to write it into another sheet
where I am tracking the row using my variable iCount - but would like to
change (when required) the Column - the "S" in "Sheet4.Range("S" &
iCount).Value".

Thanks again