Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 42893 times)

Offline yitzf

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2473
  • Total likes: 4
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1050 on: September 27, 2017, 02:02:30 PM »
Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?

I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]
Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6515
  • Total likes: 77
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1051 on: September 27, 2017, 02:56:48 PM »
I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]
Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

Offline yitzf

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2473
  • Total likes: 4
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Help and Problems
« Reply #1052 on: September 27, 2017, 02:58:45 PM »
Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6515
  • Total likes: 77
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1053 on: September 27, 2017, 03:14:28 PM »
Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.
Making the macro, adding it, making sure it doesn't screw up your life (many macros do ime) etc