Results 1 to 3 of 3
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Selection.SpecialCells (XL97Sr2h)

    Following from an older thread, I'm trying to develop a macro which changes numbers formatted as text to numbers formatted as values. (The format change is stripped from the code below, since it works fine.)

    One of John Walkenbach's books gave me the idea of selecting a subset of the selected range to make the code more efficient. However, the problem I'm having is that the red line of code Selection.SpecialCells ignores the first column of the manually selected range. Manually using Selection.SpecialCells().Select gives the same result; first column is not selected. Can someone reproduce this behavior? What am I missing?

    Sub Text2Values()
    Dim objNumberCells As Object
    Dim rngCell As Range
    On Error Resume Next
    <font color=red> Set objNumberCells = Selection.SpecialCells(xlCellTypeConstants, xlNumbers).Select</font color=red>
    For Each rngCell In objNumberCells
    rngCell.Value = rngCell.Value
    Next rngCell
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Selection.SpecialCells (XL97Sr2h)

    John

    It is obvious that Excel does not recognise the first column as Constants. A couple of days ago I got a situation similarly to yours, I think, where Excel did not recognise empty cells. These cells were a Query dump from a database. Have you tried some other special selection, say Formulas and text?

    If you select the range, one column larger, and then say Selection.SpecialCells().Select what happens?

    Also I always use Variant for For-Each loops, just something I read some place.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Selection.SpecialCells (XL97Sr2h)

    Thanks, Wassim, that first column skip is poor behavior on the part of SpecialCells(xlCellTypeConstants, xlNumbers). A quick test shows that selecting a column to the left doesn't help unless it contains constants. FWIW here's my final code which seems to be reasonably efficient and robust.

    Sub Text2Values()
    Application.ScreenUpdating = False
    Dim objNumberCells As Object
    Dim obj1stCol As Object
    Dim rngCell As Range
    With Selection
    .NumberFormat = "General"
    .Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns
    End With
    On Error Resume Next
    Set objNumberCells = Selection.SpecialCells(xlCellTypeConstants, xlNumbers).Select
    If Not Err Then
    For Each rngCell In objNumberCells
    rngCell.Value = rngCell.Value
    Next rngCell
    End If
    Set obj1stCol = Selection.Columns(1).Address.Select
    On Error Resume Next
    Set objNumberCells = obj1stCol.SpecialCells(xlCellTypeConstants, xlNumbers).Select
    If Not Err Then
    For Each rngCell In objNumberCells
    rngCell.Value = rngCell.Value
    Next rngCell
    End If
    ActiveCell.Select
    Application.ScreenUpdating = True
    End Sub

    Suggestions for improvement welcome.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •