excel 2003 vba: multi-cell (variable length) reference to a single cell

[BEEJAYII]

Greetings: First post in this forum.
If I overlook any of the rules, please let me know quick.

Have a range of cells in Column E. Alway starting on E6, but number or rows unknow/variable.
Each of the E6 and Down cells need to read +Cxx - where xx is the last row in column C.
I've included extracts of my code (and was going to send sample copy - Not available to me?). This will hopefully clarify exactly what I am trying to acomplish.

Sub Test_JFS()
Dim LastRow As Long
 
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
 
'   Copy C Column VALUES to F6 and down
      Range("F6:F" & LastRow).Value = Range("C6:C" & LastRow).Value
 
'   Sum Column C
      Cells(LastRow + 1, "C").Formula = "=Sum(C6:C" & LastRow & ")"
 
'   Count # of Units(rows) in Column C
      Cells(LastRow + 2, "C").Formula = "=Count(C6:C" & LastRow & ")"
 
'   Calculate Average: Sum divided by # of Units
      Cells(LastRow + 3, "C").Formula = "=R[-2]C/R[-1]"
 
'   HERE is Where I'm Stuck
'   Range E6 & Down to End to = Range C6 EndDown (Use last used cell)
'   Cells in Column E must read  +C14 (last row), as reference
 
'   The following comes up with a Compile Error: A Syntax Error
        Range("E6:E" & LastRow).Formula = "=Formula("C6").End(xldown)
End Sub

If I can find figure out how to do attachments, I will yet add it.

 

[Norie]

Do you want to fill E6 down to the last row of data?

Is this the formula you want to use, where xx is the last row in column C?

In E6, =C6
In E7, =C7
...
In Exx, =Cxx

 

[BEEJAYII]

Norie:

1: Column C is Source
2: Column E is Destination

E needs to be 'linked' to C.
Therefore, when C changes, E will also change
Cells in E will actually read =+Cxx

Sorry about my poor descriptions.
Hopefully this is better.

 

[Norie]

Sorry I'm confused.

What do you maen by =+Cxx?

Do you just want that as text or is it a formula?

If it is a formula the + isn't required.

Perhaps if you posted some examples of the formula for a couple of cells.

PS Are you sure you don't want what I described?

 

[BEEJAYII]

Let me try again.....

1: Column C is Source
2: Column E is Destination
E needs to be 'linked' to C.
Therefore, when C changes, E will also change

Cells in E will actually read =+Cxx:
Should have said:
When hi-liting a cell in column E, the formula bar will show
=+C6
=+C7
=+C8
Etc.
Then, when the source is changed, the destination will also change.

The following code works (almost ), but it needs to create the link, rather than doing just a value copy.

'   THE FOLLOWING IS NOT CORRECT
'   E6 and column down, NEEDS TO BE LINKED TO C6 END DOWN
'      Copy "Variable" Cell to Column E6 and all required cells in Column
 
            Range("E6") = Range("C6").End(xlDown)
            Range("E6").AutoFill Destination:=Range("E6:E" & LastRow)

Does this help?

 

[Norie]

That's exactly what I suggested earlier apart from the + which isn't needed.

Anyway try this.

Dim LastRow As Long
 
        LastRow = Range("C" & Rows.Count).End(xlUp).Row
 
        Range("E6:E" & LastRow).Formula = "=C6"

 

[BEEJAYII]

Norie:
1: All my posts from yesterday disappeared somehow.
2: I've got myself confused in this process, as well.
----Somehow I've been mixing up two seperate problems that I've been working on. SORRY!!

I appreciate the efforts you made. Since I'm obviously not explaining the situation well at all, I am going to try posting on a forum that allows me attachments.

I don't know if there is someway to just totally delete this post.
Due to my muck ups, it is of no value to anyone.

Again, Thanks for your work.

 

[Norie]

One question, did you try the code I posted?

PS Why not upload your workbook to a file-sharing site, eg BoxNet?

Then you can post a link to the file here.