Press Enter on Each Cell in a Sheet

[parr.a.pluie]

Hello,

I entered a formula into cells using a formula that I wrote myself using VB. It's important to note that each cell's formula is different. For example,

c1: =upper(dog)
c2: =upper(cat)
c2: =upper(horse)

The problem is that because I entered these formulas using a VB formula, I have to go through each cell and press enter in order to calculate the result (otherwise the cell is showing the formula not the result), and I have more than 33000 cells.

Does anyone know of a way to do this automatically (i.e. "press enter" in multiple cells at once)? I tried F2 and then crt+enter, but it changes every cell to the first cell's formula and as I mentioned earlier, each cell's formula is different.

Thank you in advance!

 

[parr.a.pluie]

baha, I just stuck spoons in the keyboard to hold down the F2 and enter keys... not as elegant, but it works. :P

 

[Smitty]

Welcome to the Board!

Can you post your code?

Spoons really shouldn't be necessary.

One thing you can do is select the entire range and goto Data-->Text to Columns-->Finish.

HTH,

 

[parr.a.pluie]

haha, much more elegant. Thanks a lot that worked perfectly!

Thanks!

 

[lenze]

The Proper way using VBA is

= UCsse(dog)

It does not place a formula in the cell. It just converts it!!

lenze

 

[Norie]

You should not need to F2 or use Data>Text to columns, though I'm sure they will work.

There must be something else going on, for example calculation not set to Automatic, the cells with the formula being formatted as text...

Might even be a conflict with the built-in UPPER worksheet function, that is if you've created a UDF called Upper.

 

[lizgoodman81]

Hello,

I entered a formula into cells using a formula that I wrote myself using VB. It's important to note that each cell's formula is different. For example,

c1: =upper(dog)
c2: =upper(cat)
c2: =upper(horse)

The problem is that because I entered these formulas using a VB formula, I have to go through each cell and press enter in order to calculate the result (otherwise the cell is showing the formula not the result), and I have more than 33000 cells.

Does anyone know of a way to do this automatically (i.e. "press enter" in multiple cells at once)? I tried F2 and then crt+enter, but it changes every cell to the first cell's formula and as I mentioned earlier, each cell's formula is different.

Thank you in advance!

I know that this is a very old thread, but I wanted to provide an easy solution in case anyone finds it while trying to solve their own problem.

When Excel won't recognize the formula until Enter is pressed in each cell, you can achieve the same effect by doing a find and replace on the entire sheet. Replace "=" with "*****". Then do another search and replace and change "*****" back to "=". Each cell will refresh and your formula will be recognized.

 

[kalyank854]

Hope the below code works for you

Sub abc()
    N = InputBox("Enter the number of columns", vbOKOnly, "100")
          
    
    For i = 1 To N
    
    Columns(i).Select
    
    Selection.TextToColumns Destination:=Cells(1, i), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
Next i
End Sub