Press Enter on Each Cell in a Sheet

parr.a.pluie

New Member
Joined
Aug 6, 2010
Messages
3
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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

parr.a.pluie

New Member
Joined
Aug 6, 2010
Messages
3
baha, I just stuck spoons in the keyboard to hold down the F2 and enter keys... not as elegant, but it works. :P
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

The Proper way using VBA is
Code:
= UCsse(dog)
It does not place a formula in the cell. It just converts it!!

lenze
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,763
Office Version
365
Platform
Windows
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

New Member
Joined
May 3, 2016
Messages
1
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

New Member
Joined
Sep 19, 2018
Messages
2
Hope the below code works for you

Code:
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,460
Messages
5,511,503
Members
408,853
Latest member
JoshuaHudsonpTi45

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top