How do I sort worksheet without affecting formula values?

  • Thread starter Thread starter HELP PLEASE!
  • Start date Start date
H

HELP PLEASE!

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