Macro Looping and If Statement Help

[bl79]

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

 

[cowski]

For starters:

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

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

 

[cowski]

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.

 

[bl79]

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

 

[IRstuff]

Delete the brackets.

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[bl79]

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.

 

[GregLocock]

I don't think

ActiveCell = "#DIV/0!"


is a valid test, except as a textual comparison. DIV/0 is a cell state not a string

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[IDS]

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:

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/

 

[IDS]

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:

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/

 

[bl79]

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.

 

[IDS]

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:

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

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/