How to find errors in documents
when you have a series of data and you have a space or error in your numbers
when you are using functions and formulas. how do you find where there error is in the data if you cant see the space or cant see where the error might be that is causing the formula answer to be wrong. |
How to find errors in documents
Hi Pnoahjones,
Numbers with spaces are treated by Excel as text. Provided you're using the default cell format, rather than left-aligned, the text strings will be right-aligned. You can clean up a selected range, or a whole worksheet using the macro below: Sub TrimRange() Dim SBar As Boolean, Cell As Range, CellCount As Long, I As Long SBar = Application.DisplayStatusBar Application.DisplayStatusBar = True Application.ScreenUpdating = False Application.Calculation = xlManual On Error Resume Next I = 0 If Selection.Rows.Count * Selection.Columns.Count 1 Then CellCount = Selection.Rows.Count * Selection.Columns.Count Else CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count End If For Each Cell In Selection.SpecialCells(xlConstants) Cell.Value = Application.Trim(Replace(Cell.Value, Chr(160), " ")) I = I + 1 Application.StatusBar = Int(I / CellCount * 100 + 0.5) & "% Trimmed" Next Cell Application.Calculation = xlAutomatic Application.StatusBar = False Application.DisplayStatusBar = SBar Application.ScreenUpdating = True MsgBox "Finished trimming " & CellCount & " cells.", 64 End Sub The macro gives a progress report on the Excel status bar and a message box when it's finished. -- Cheers macropod [Microsoft MVP - Word] "Pnoahjones" wrote in message ... when you have a series of data and you have a space or error in your numbers when you are using functions and formulas. how do you find where there error is in the data if you cant see the space or cant see where the error might be that is causing the formula answer to be wrong. |
All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com