Create formulas using VBA

[Phil_Evans]

Hi,

I'm trying to create a enter a formula into a cell as part of a VBA macro.

The tricky part seems to be that I want part of the formula to refer to a cell on a new sheet. The name of the new sheet is a variable ...Thats where it stops working...

This is what I have;
Cells(x,y)= "=" & new_sheet_name & "!a1*45"

Any ideas?

Cheers, Phil

 

[Russell Hauf]

On 2002-10-31 13:42, Phil_Evans wrote:
Hi,

I'm trying to create a enter a formula into a cell as part of a VBA macro.

The tricky part seems to be that I want part of the formula to refer to a cell on a new sheet. The name of the new sheet is a variable ...Thats where it stops working...

This is what I have;
Cells(x,y)= "=" & new_sheet_name & "!a1*45"

Any ideas?

Cheers, Phil

Use the .Formula property of the Cells object. Something like this:

    ActiveSheet.Cells(x, y).Formula = "=" & new_sheet_name & "!a1*45"

Hope this helps,

Russell

 

[babs]

Before that formula use

Sheets(new_sheet_name).Select

It will then select the new sheet and enter the value in the cell.

 

[Russell Hauf]

On 2002-10-31 14:01, babs wrote:
Before that formula use

Sheets(new_sheet_name).Select

It will then select the new sheet and enter the value in the cell.

You don't actually have to select a sheet to enter a formula. But in my example, I just put ActiveSheet.Cells - so it would put the formula in the sheet that was last selected. When putting formulas in multiple sheets, it is much faster to not select the sheets. Just to clear things up...

 

[Phil_Evans]

Hi guys,

Thanks for the suggestions ... unfortunately they didn't help ...

I'll give a bit more background - maybe that will help.

My macro starts by creating a new sheet. I want to create a formula on another sheet referencing a cell on the new sheet. I can store the name of the new sheet in a variable (new_sheet_name).

Then I have;

With Sheets("Summary")
.cells(i,j).formula = "=""Site Name is ""&new_sheet_name!e7"

And it doesn't recognise the variable ...


This is all for interest only now as I have got around the problem by temporarily renaming all new sheets so I can make the formula the same every time. I then rename the new sheet and it changes the formula to match the change in new sheet.

Cheers, Phil

 

[Russell Hauf]

When concatenating strings and variables and numbers in VBA, you need the & operator.

So let's say you have a variable named intSomeNum, and that intSomeNum = 5.

The following expression:

"My number is " & intSomeNum & ". What is yours?"

Would evaluate to:

My Number is 5. What is yours?
====================================

If you want "Site name is" in your cell, you aren't really entering a formula, you are entering a string. So in that case, you do NOT want to use the formula property.

You'd do something like this:

With Sheets("Summary")
.Cells(i, j) = "Site Name is " & Worksheets(new_sheet_name).Range("E7").Value

HTH,

Russell