Nested IF problem

  • Thread starter Thread starter azu_daioh
  • Start date Start date
A

azu_daioh

Can anyone take a look at the code please and help me figure out why
it's skipping every other "elfseif" statement?

lets say I have these values on each row (its a copy and paste from a
dumb terminal) which I'm trying to reformat in Excel because we can't
import the data directly into Excel (or at least our programmers won't
provide us with instructions).

VIEW 2.0
COMMAND ===>
*********************************************************** TOP OF
DATA
..SARPAGE
1
..
..ROUTED ALL
..REPORT
..PROGRAM
.. *** FOR MONTH
.. DOCTOR
.. LICENSE
.. NUMBER
.. -------- --------------------------------
***********************************************************BOTTOM OF
DATA

For some reason it will delete the 1, 3, 5, 7, etc rows matching the
values indicated in the IF statements.

Thank you,
Sharon

---------
Sub delete_unwanted_row()
Dim x As Long
Dim lRow As Long
x = 1 'row
lRow = InputBox("Enter Last Row")

For oRow = 1 To lRow

If Cells(x, 1).Value Like "*" & "VIEW" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "COMMAND" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "OF DATA" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "SARPAGE" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "ROUTED" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "REPORT" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "PROGRAM" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "MONTH" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "DOCTOR" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "LICENSE" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "NUMBER" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
ElseIf Cells(x, 1).Value Like "*" & "----" & "*" Then
Range(Cells(x, 1), Cells(x, 1)).Select
Selection.EntireRow.Delete
End If

x = x + 1

Next oRow


End Sub
 
nevermind. I figured it out. I misplaced the x=x+1, it should be
after ELSE and before END IF.

Thanks anyway. :)
 
Excel gets confused with a For Each (row) when rows are being deleted.
Rather than

For oRow = 1 To lRow

try

For lRow to 1 Step -1

--
Jim
| Can anyone take a look at the code please and help me figure out why
| it's skipping every other "elfseif" statement?
|
| lets say I have these values on each row (its a copy and paste from a
| dumb terminal) which I'm trying to reformat in Excel because we can't
| import the data directly into Excel (or at least our programmers won't
| provide us with instructions).
|
| VIEW 2.0
| COMMAND ===>
| *********************************************************** TOP OF
| DATA
| .SARPAGE
| 1
| .
| .ROUTED ALL
| .REPORT
| .PROGRAM
| . *** FOR MONTH
| . DOCTOR
| . LICENSE
| . NUMBER
| . -------- --------------------------------
| ***********************************************************BOTTOM OF
| DATA
|
| For some reason it will delete the 1, 3, 5, 7, etc rows matching the
| values indicated in the IF statements.
|
| Thank you,
| Sharon
|
| ---------
| Sub delete_unwanted_row()
| Dim x As Long
| Dim lRow As Long
| x = 1 'row
| lRow = InputBox("Enter Last Row")
|
| For oRow = 1 To lRow
|
| If Cells(x, 1).Value Like "*" & "VIEW" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "COMMAND" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "OF DATA" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "SARPAGE" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "ROUTED" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "REPORT" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "PROGRAM" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "MONTH" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "DOCTOR" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "LICENSE" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "NUMBER" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| ElseIf Cells(x, 1).Value Like "*" & "----" & "*" Then
| Range(Cells(x, 1), Cells(x, 1)).Select
| Selection.EntireRow.Delete
| End If
|
| x = x + 1
|
| Next oRow
|
|
| End Sub
|
 
Back
Top