cells function

[Raul Sousa]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick

 

[Joel]

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)

 

[JW]

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?

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)

 

[JW]

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)





- 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.

 

[luis_excelkid]

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...

 

[Raul Sousa]

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