Create formulas using VBA

Phil_Evans

New Member
Joined
Oct 30, 2002
Messages
2
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Russell Hauf

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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:

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

Hope this helps,

Russell
 

babs

Board Regular
Joined
Oct 30, 2002
Messages
106
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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... :biggrin:
 

Phil_Evans

New Member
Joined
Oct 30, 2002
Messages
2
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
1,611
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
 

Forum statistics

Threads
1,144,274
Messages
5,723,442
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top