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
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
CODE
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 = 1i = i + 1
Loop
n = n + 1
Loop
End Sub
Sub Macro1()For n = 1 To
n =30For i = 1 To
i =30Cells(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
RE: Macro Looping and If Statement Help
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
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Macro Looping and If Statement Help
RE: Macro Looping and If Statement Help
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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?
RE: Macro Looping and If Statement Help
One way to check for cells with an error value is to use the Worksheetfunction.IsError function:
CODE
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
CODE
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
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
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
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
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/