Results 1 to 7 of 7

Thread: [RESOLVED] Skip cells containing text [Excel]

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Resolved [RESOLVED] Skip cells containing text [Excel]

    Hi Guys,

    My code have to run though a range of cells in Excel and multiply all values by a factor 0.6.
    I need to skip all cells withing the range that contain words(text) or formulas.
    My current code manages to skip cells formatted as text or that contains formulas, but not cells containing words and formatted as general.

    HTML Code:
    mTextFormat = "@"
        For mRunner = 1 To mCell.Column - 1
            Set aCell = Cells(mCell.Row, mRunner)
                On Error GoTo SubErrHandler
                    If aCell.Value = 0 Then
                        ElseIf aCell.NumberFormat = mTextFormat Then
                        ElseIf Left$(aCell.Formula, 1) = "=" Then '-- cell does not contain a formula
                        Else
                            aCell.Formula = "=" & aCell.Formula & "*" & mCell
                    End If
    SubErrHandler:
        Next
    This leaves me with some cells as "#NAME?"

    Please assist.

  2. #2
    Addicted Member
    Join Date
    Jul 2008
    Location
    Colorado
    Posts
    193

    Re: Skip cells containing text [Excel]

    You could always use the below code to just run it if it is a number.

    Code:
    If Application.IsNumber(aCell.Value) = True Then
    Edit: I think you would put this after checking if it's a formula...
    Last edited by Fizziii; Jun 11th, 2009 at 09:10 AM.

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: Skip cells containing text [Excel]

    I knew it had to be simple! Thanks a lot.

    If I am allowed a follow up question:
    Can one split the for loop to skip a certain value, for example:

    For x = 1 to 5 and 7 to 10

    At the moment I am repeating the code to achieve the same:
    For x = 1 to 5
    ....
    ....
    For x = 7 to 10

    But would be more elegant if I could run all in one loop?

    Regards

  4. #4
    Addicted Member
    Join Date
    Jul 2008
    Location
    Colorado
    Posts
    193

    Re: Skip cells containing text [Excel]

    This is the code I was testing with. I'm pretty sure you can replace "range(myrange)" with "aCell"

    Code:
    If range(myrange).Value > 1 And range(myrange).Value < 6 Or _
               range(myrange).Value > 6 And range(myrange).Value < 10 Then

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: Skip cells containing text [Excel]

    Not exactly what I am after, I am specifically wanting to use the "for" command, broken into 2 sections.

    Please note that this is unrelated to the first question in my post.

  6. #6
    Addicted Member
    Join Date
    Jul 2008
    Location
    Colorado
    Posts
    193

    Re: Skip cells containing text [Excel]

    Sorry for the confusion. I know it could work if you put an if statement and put everything in that...

    Code:
    For mRunner = 1 To mCell.Column - 1
            If mRunner = 6 Then
            Else
               Set aCell = Cells(mCell.Row, mRunner)
                   On Error GoTo SubErrHandler
                       If aCell.Value = 0 Then
                           ElseIf aCell.NumberFormat = mTextFormat Then
                           ElseIf Left$(aCell.Formula, 1) = "=" Then '-- cell does not contain a formula
                           Else
                               aCell.Formula = "=" & aCell.Formula & "*" & mCell
                       End If
          End If
    SubErrHandler:
           Next

  7. #7

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: Skip cells containing text [Excel]

    Thanks

Posting Permissions

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



Click Here to Expand Forum to Full Width