HOW to include ActiveSheet vatiable in formula?

**madars** · 01-28-2011, 05:19 AM

I have set variable for active sheet!

Set s = ActiveSheet

I can't get formula work for sheet as a variable

???? ActiveCell.FormulaR1C1 = "=s!R[-20]C[1]" ????

How do i force formula from variable sheet???????

**antoka05** · 01-28-2011, 06:22 AM

You could do so:

Please Login or Register  to view this content.

Regards,
Antonio

**madars** · 01-28-2011, 06:29 AM

This didn't work... I got syntax error!

**antoka05** · 01-28-2011, 06:33 AM

This macro runs fine:

Please Login or Register  to view this content.

In the above code the important is that you use 's' variable to set a worksheet because if you use variable only for the name you need to use this other code:

Please Login or Register  to view this content.

Regards,
Antonio

**madars** · 01-31-2011, 03:41 AM

It works in separate excel but not in this code... Why?

Please Login or Register  to view this content.

Mod. edit: CODE tags when posting VBA please - per Forum Rules

**DonkeyOte** · 01-31-2011, 04:14 AM

Without looking at your code in detail - when posting references to sheets always wise to encase within '

Please Login or Register  to view this content.

Please also note my edit to your prior post - any/all VBA should be encased within CODE tags per Forum Rules.

**DonkeyOte** · 01-31-2011, 04:20 AM

On an aside - when posting formulae to the worksheet it's worth being aware of the fact that VBA is US-centric.
This means the .Formula property assumes US delimiters - if you wish/need to post formulae with delimiters per your own locale use .FormulaLocal property.

**madars** · 01-31-2011, 04:21 AM

Works great! Thanks!

Originally Posted by DonkeyOte

Without looking at your code in detail - when posting references to sheets always wise to encase within '

Please Login or Register  to view this content.

Please also note my edit to your prior post - any/all VBA should be encased within CODE tags per Forum Rules.

**pike** · 01-31-2011, 04:58 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save