×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Macro Looping and If Statement Help

Macro Looping and If Statement Help

Macro Looping and If Statement Help

(OP)
OK, I am having a lot of trouble utilizing loops and if statements. I have some experience with programming but for some reason writing these macros is killing me.

I just want to create a macro that will go through every single cell and check it to see if X is in that cell. If X is in the cell then change it to Y and continue to the next cell and if not then move to the next cell.

Ive tried several ways of doing this but none seem to work for me. Could someone help me out.

For example Ive tried to delete the #DIV/0! error(I know there are easier ways but I would like to come up with a general method as I described for other circumstances.

My several faulty codes:

Sub delete()
Do While n < 30
    Do While i < 30
    If Cells(i, n).Value = "#DIV/0!" Then [Cells(i,n).Value=""]
    i = i = 1
    Loop
    n = n + 1
Loop

End Sub

Sub Macro1()
For n = 1 To n = 30
    For i = 1 To i = 30
        Cells(i, n).Select
            If ActiveCell = "#DIV/0!" Then
                ActiveCell.Value = ""
            End If
    Next i
Next n
End Sub


Again, I know there are more efficient ways but I see no reason why this wouldn't work. Clearly Im just a newb

RE: Macro Looping and If Statement Help

For starters:

CODE

Sub delete()
Do While n < 30
    Do While i < 30
    If Cells(i, n).Value = "#DIV/0!" Then [Cells(i,n).Value=""]
      Cells(i,n).Value=""
    End if

    i = i = 1
    i = i + 1
    Loop
    n = n + 1
Loop
End Sub

Sub Macro1()For n = 1 To n = 30
    For i = 1 To i = 30
        Cells(i, n).Select
            If ActiveCell = "#DIV/0!" Then
                ActiveCell.Value = ""
            End If
    Next i
Next n
End Sub

RE: Macro Looping and If Statement Help

oops, Sub Macro1() should be on its own line and For n = 1 To 30 on the next line. Also, in Sub delete(), i and n are not declared/initialized; VBA might forgive you for this or yield unexpected results with no warning. It is better to declare variables and initialize them (or pass values into your subroutine) to be sure you get what you want.

RE: Macro Looping and If Statement Help

(OP)
Thanks for the help. Whoops, seem like some rookie mistakes although I tried many iterations. Trying your correction now I still get an error 13 Type Mismatch.

The code is as follows

Sub mac1()
For n = 1 To 30
    For i = 1 To 30
        Cells(i, n).Select
            If ActiveCell = "#DIV/0!" Then
                [ActiveCell.Value = ""]
            End If
    Next i
Next n
End Sub

 
It seems upset with the If statement

RE: Macro Looping and If Statement Help

(OP)
Still no good. I put the brackets in thinking they would help. I get the same error. I've no idea why this doesn't work. I appreciate your help though.

RE: Macro Looping and If Statement Help

As Greg said,  #Div/0! is an error value, not a string, so it is not equal to "#Div/0!"

One way to check for cells with an error value is to use the Worksheetfunction.IsError function:


CODE

Sub CheckDivZ()
Dim Cell As Range
For Each Cell In Selection
If WorksheetFunction.IsError(Cell.Value) = True Then
Cell.Value = ""
End If
Next
End Sub

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Macro Looping and If Statement Help

A more efficient way is to copy the worksheet range into a variant array, check for values that are not of type "Double" (case sensitive), then write the corrected array back to the worksheet:

CODE

Sub CheckDivZ()
Dim RangeVals As Variant, i As Long, NumRows As Long
RangeVals = Selection.Value2
NumRows = UBound(RangeVals)

For i = 1 To NumRows
If TypeName(RangeVals(i, 1)) <> "Double" Then
RangeVals(i, 1) = ""
End If
Next i
Selection.Value2 = RangeVals
End Sub

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Macro Looping and If Statement Help

(OP)
Thanks so much for the help. For some reason Excel(2003) finds error with:

NumRows = UBound(RangeVals)

I commented out the line and it does not find any other errors. I'm not quite sure why the line is not working. I get a Run-time error 13 type mismatch.

Additionally, would it be possible for me to say highlight certain cells that exceed a value with this formula? It seems ike find-replace should be able to do this but for some reason when I use replace I can only search for formulas and not values.  

RE: Macro Looping and If Statement Help

Quote:


Thanks so much for the help. For some reason Excel(2003) finds error with:

NumRows = UBound(RangeVals)

The problem is that the code was designed to work on a selection of 2 or more cells, so the line:
RangeVals = Selection.Value2
returns an array, but if there is only a single cell selected this line returns the value of the contents of the cell, so the line:
NumRows = UBound(RangeVals)
does not work.  

If you select two or more cells in a single column then the code should work, but I have amended the code to deal with the single cell case:

CODE

Sub CheckDivZ()
    Dim RangeVals As Variant, i As Long, NumRows As Long

    If Selection.Rows.Count = 1 Then
        If TypeName(Selection.Value2) <> "Double" Then
            Selection.Value2 = ""
        End If
        Exit Sub
    End If

    RangeVals = Selection.Value2
    NumRows = UBound(RangeVals)
    For i = 1 To NumRows
        If TypeName(RangeVals(i, 1)) <> "Double" Then
            RangeVals(i, 1) = ""
        End If
    Next i
    Selection.Value2 = RangeVals
End Sub

Quote:


Additionally, would it be possible for me to say highlight certain cells that exceed a value with this formula? It seems ike find-replace should be able to do this but for some reason when I use replace I can only search for formulas and not values.  

You could, but it would be easier to use the "conditional formatting" feature, which is designed to do exactly what you want.

Also note that if you want to find cells with a specific value you can search on values, rather than searching the formulas.  Look in the search dialog box options.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources