Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Nested IF problem

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
azu_daioh@yahoo.com - 12 Sep 2007 22:02 GMT
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
azu_daioh@yahoo.com - 12 Sep 2007 22:11 GMT
nevermind.  I figured it out. I misplaced the x=x+1, it should be
after ELSE and before END IF.

Thanks anyway. :)
Jim Rech - 12 Sep 2007 22:12 GMT
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

Signature

Jim

| Can anyone take a look at the code please and help me figure out why
| it's skipping every other "elfseif" statement?
[quoted text clipped - 80 lines]
|
| End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.