Cell references in excel vba code

  • Thread starter Thread starter Isis
  • Start date Start date
I

Isis

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
 
Add defined names to the columns row 1 and use these

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

for example
 
Add defined names to the columns row 1 and use these

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

for example
Click to expand...

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
 
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 = ...
 
Back
Top