Simple Loop

I have a spreadsheet with 495 rows and 12 columns full of equations that I wish to edit and calculate and then proceed to the next equation in the row below the existing line. I wish to then have this simple loop continue down the page to the end of the column. :(

example:

place cursor on an equation (any column) press {F2} then {F9} and {down}, repeat this loop 495 times?

I've used this simple loop in basic and Lotus 123. I'm not well versed in Visual Basic within Excell. :confused:

Do you have the power to translate the above loop to a working excell macro? "Oh mighty one"! :o

For some reason, this last line does not work. The macro stops at this line

Selection.pastespecial paste:= xlpastevalues, operations: xlnone, skip blanks_:= false, transpose:= false

Any suggestions?

Here's where the code stops......

"see attachment"

You can also try this if you like:

VB Code:

Sub PlaceValues()
    Dim cell As Excel.Range, rng As Excel.Range
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    For Each cell In rng
        cell.Value = cell.Value
    Next
End Sub

Hope this helps

Thank you for your coding. As I used the macro one result became clear. The value of equation does not appear. For instance.....

If , in cell (AA4), I place the equation........... '=+'Chem Engrg'!D5
.....then if I use the macro, the equation does not calculate to the value in cell D5 from Sheet 'Chem Engrg'.
.....nor does pressing [F2] and then [F9] , with cursor placed on cell, change the equation to a value. The equation..... '=+'Chem Engrg'!D5 remains.

If however, in another cell, I write........... =right(AA4,17)
the cell will give me the equation............ =+'Chem Engrg'!D5
....then when I place my cursor on the new equation and press [F2] and then [F9], the value of cell 'Chem Engrg'!D5 appears. (real number 123.1)

The macro does not remove the " ' " from the text '=+'Chem Engrg'!D5

There may be an easier way to produce the desired result. However, I do not know of Visual Basics capabilities.

How do I tweek the macro to allow the value of the cell to apprear rather than the equation?

Quote:

---

Originally Posted by The Webster

Thank you for your coding. As I used the macro one result became clear. The value of equation does not appear. For instance.....

If , in cell (AA4), I place the equation........... '=+'Chem Engrg'!D5
.....then if I use the macro, the equation does not calculate to the value in cell D5 from Sheet 'Chem Engrg'.
.....nor does pressing [F2] and then [F9] , with cursor placed on cell, change the equation to a value. The equation..... '=+'Chem Engrg'!D5 remains.

If however, in another cell, I write........... =right(AA4,17)
the cell will give me the equation............ =+'Chem Engrg'!D5
....then when I place my cursor on the new equation and press [F2] and then [F9], the value of cell 'Chem Engrg'!D5 appears. (real number 123.1)

The macro does not remove the " ' " from the text '=+'Chem Engrg'!D5

There may be an easier way to produce the desired result. However, I do not know of Visual Basics capabilities.

How do I tweek the macro to allow the value of the cell to apprear rather than the equation?

---

Did you try my solution posted above?

Yes!
...and the dilemma continues.

did you try converting text to columns?

Hi

Quote:

---

If , in cell (AA4), I place the equation........... '=+'Chem Engrg'!D5...

---

If any equation/formula in a cell starts with an apostrophe, then it will not be considered as an equation. It will be considered as a text.

For example if you put '55 in a cell, it will be considered as text not a number... same goes for '=sum(A1:A2)...

BINGO! :wave:

That does the trick! :thumb:

You're a Genius! :D

Glad to see you got it working :thumb: