Prev: Lost a radio button on a sheet
Next: statistical database attack verses defense for online game
From: HELP PLEASE! HELP on 13 Mar 2010 14:46
I have two worksheets, one of which has formulas which reference the other.
The problem is, I cannot sort the first worksheet and have the formula values
in the second worksheet follow it so the values stay the same. I have tried
making them absolute references ($A$1) but it doesn't help.
From: Dennis Tucker on 13 Mar 2010 20:15
I feel your pain. I've ran into the same issue before.

The problem is the use of worksheet functions. The solution is DO NOT USE
worksheet functions. Use a VBA subroutine instead.

Here is an example:

With worksheet functions....

A B C
1 2 3 6(formula =A1*B1)
2 4 5 20(formula =A2*B2)
3 6 7 42(formula =A3*B3)
..
..

Without worksheet functions, using VBA Sub....

A B C (Start
Button)
1 2 3
2 4 5 User clicks
the start button and the macro runs.
3 6 7

A B C (Start
Button)
1 2 3 6
2 4 5 20
3 6 7 42

The Macro Code

Sub MultiplyMyNumbers()

For MyRow=1 to 3

Worksheets("Sheet1").Range("C"+Cstr(MyRow)).Value =
Worksheets(("Sheet1").Range("A"+Cstr(MyRow)).Value *
Worksheets(("Sheet1").Range("B"+Cstr(MyRow)).Value

Next MyRow

End Sub





"HELP PLEASE!" <HELP PLEASE!@discussions.microsoft.com> wrote in message
news:CF8032D2-204E-4F0C-ACF7-4B3A3D56D22A(a)microsoft.com...
> I have two worksheets, one of which has formulas which reference the
> other.
> The problem is, I cannot sort the first worksheet and have the formula
> values
> in the second worksheet follow it so the values stay the same. I have
> tried
> making them absolute references ($A$1) but it doesn't help.

 | 
Pages: 1
Prev: Lost a radio button on a sheet
Next: statistical database attack verses defense for online game