Cell References in VBA

[Tills13]

Good Afternoon,

I'm writing a little program for my school and I've run into a small problem.

The code in question is:

Range(Cells(19, 1), Cells(19, 3)).Copy
Range(Cells(19 + Count, 1), Cells(19 + Count, 3)).PasteSpecial (xlPasteFormats)
Cells(19 + Count, 1).Value = Cells(18 + Count, 1).Value + 1
Cells(19 + Count, 2).Value = CompleteString
Cells(19 + Count, 3).Formula = "(this is where I'm stuck)"

Where I've written "this is where I'm stuck" I'd like to have a reference to another cell, it will just duplicate the value in that cell by referencing it. However, I cannot figure out how to do that.

I don't think I'm being clear enough, so I'll give you an example. Other code in my project adds a pre-formatted area where a user would input a number of values and it would sum them in a "totals" cell underneath the area. The cell I am stuck on will replicate this value in another part of the sheet.

I've tried, foolishly, I discovered,

Cells(19 + Count, 3).Formula = "=Cells(15 + (Count * 8), 11)"

but that obviously didn't work. (15 + (Count * 8), 11) is the location of the cell that I want to reference at all times. As I add areas, count increases.

 

[sykes]

Hi there, and welcome to Mr Excel!

I'd love to help, and I understand your code (well, most of it) but there are a couple of points:

1. Am I right in my thinking that you've used what appears to be your own variable called "Count"? Have you set this variable anywhere in your code, and what does it reference? The reason I'm asking is that we could do with understanding this, to be sure about the workings of the code, and the syntax.
2. The other reason for asking is that Excel has it's own built-in function called "Count" and using this name for your own variable (if you have) could cause a conflict.

It may help us if you post the whole procedure, so we can see how it all works together.

 

[RoryA]

If you use R1C1 notation, it's a fairly simple substitution:

Cells(19 + Count, 3).FormulaR1C1 = "=R"& 15 + (Count * 8) & "C11"