cells function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Raul Sousa - 19 May 2008 15:11 GMT

The resulto of the code
ActiveCell.Formula = "=" & (Cells(11, Sheets.Count + 1))
is : =
But, I need it to be equal to :
= D11, or E11, depending on the number of sheets
What am I doing wrong?

Reply to this Message

Rick Rothstein (MVP - VB) - 19 May 2008 15:27 GMT

Your question is not entirely clear to me. Are you looking for this?

ActiveCell.Formula = "=" & Chr(65 + Sheets.Count) & "11"

Rick

> The resulto of the code
> ActiveCell.Formula = "=" & (Cells(11, Sheets.Count + 1))
> is : =
> But, I need it to be equal to :
> = D11, or E11, depending on the number of sheets
> What am I doing wrong?

Reply to this Message

Joel - 19 May 2008 15:29 GMT

A formula has to look lie it would on a worksheet such as "=D11"

You can modify your code like this

ActiveCell.Formula = "=" & (Cells(11, Sheets.Count + 1).address)

or
ActiveCell.Formula = "=K" & (Sheets.Count + 1)

Reply to this Message

JW - 19 May 2008 15:37 GMT

> A formula has to look lie it would on a worksheet such as "=D11"
>
[quoted text clipped - 13 lines]
>
> - Show quoted text -

Joel, that last option wouldn't work. The "11" in the Cells function
is refering to the row number, not the column number.

Reply to this Message

JW - 19 May 2008 15:30 GMT

On May 19, 10:11 am, Raul Sousa <RaulSo...@discussions.microsoft.com>
wrote:

Right now, you are basically saying that you want the formula to equal
the value of that cell. What you need it to do is equal the address
of that cell.

For absolute references, use
ActiveCell.Formula = "=" & Cells(11, Sheets.Count + 1).Address

Or use this if you do not want absolute references
ActiveCell.Formula = "=" & Cells(11, Sheets.Count + 1).Address(False,
False)

Reply to this Message

Raul Sousa - 19 May 2008 16:05 GMT

Thank you.
It was very clear.
I need to use. adress if I need the cell's adress.

> On May 19, 10:11 am, Raul Sousa <RaulSo...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 15 lines]
> ActiveCell.Formula = "=" & Cells(11, Sheets.Count + 1).Address(False,
> False)

Reply to this Message

luis_excelkid - 19 May 2008 15:43 GMT

Hi Raul,

The problem is the "formula" is only taken the "=" symbol and not the
reference to the "Cells(11....." because "Cells(11...." represents a value,
for example, watch my lines and this it works.

Sub prueba()
aux = "B1"
Range("A1").Formula = "=" & aux
End Sub

yo just need to find a way to transform the "Cells(11...." to a text string
it represents the cell you are referencing for... if i find a way i will let
you know...

Reply to this Message