Simple Loop

[The Webster]

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.

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

[The Webster]

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?

[The Webster]

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

"see attachment"

[malik641]

You can also try this if you like:

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

[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?

[malik641]

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?

[The Webster]

Yes!
...and the dilemma continues.

[Xizeta]

did you try converting text to columns?

[Siddharth Rout]

Hi

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)...

[The Webster]

BINGO!

That does the trick!

You're a Genius!

[malik641]

Glad to see you got it working