Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Looping through cells in a range (2000/XP)

    I have several worksheets in a workbook with formulas throughout. The number of cells with formulas may represent a majority of the cells in the used range and they may represent only a few. I know how to edit the cells (in code) to replace the formulas with their result value. Because these sheets often have more cells that do not use formulas then do I would like to only have to loop or iterate through those cells instead of every cell in the worksheets used range. I can use the following code to isolate cells with formulas:
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Which retruns a Range object however I can't determine how to iterate through the cells in that range.

    Any suggestions???

    Thanks in advance
    Ed

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping through cells in a range (2000/XP)

    This works for me:

    Sub testspecialcellsfornext()
    Dim ocell As Range
    For Each ocell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
    With ocell.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    Next
    End Sub

    any good?

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Looping through cells in a range (2000/XP)

    Here's another example. I usually tack the Cells method on the end because there have been times when I didn't get single cells back. HTH --Sam
    <pre>Option Explicit

    Sub Macro1()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, 23).Cells
    MsgBox c.Formula
    Next c
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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