Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Documents move is generating link errors in Excel | Excel Discussion (Misc queries) | |||
Find and Replace Lookup errors | Excel Discussion (Misc queries) | |||
cannot find c:\documents and settings | Excel Discussion (Misc queries) | |||
To find errors | New Users to Excel | |||
Print errors with Word documents in Excel spreadsheets | Excel Discussion (Misc queries) |