Mulitple IF statements over write each other?

f3racer

New Member
Joined
Feb 25, 2009
Messages
10
Here is the problem I am running into.

I sorting data into different tabs based on certain criteria but some data might need to go into 2 tabs.

For instance, Voicemail Migration & Telecommuters.

If the data = 1000, 1888, 1999 it needs to be placed in the Voicemail Migration Tab.

If the data = */* (any number on either side of /) then needs to be placed in the Telecommuters tab.

But if the data = 1000/*, 1888/* or 1999/* than the data should be place into both Voicemail Migration and Telecommuters.

The problem I am having is if I place a IF */* Then copy for Telecommuters and for Voicemail it will only copy the data to one of the tabs and not both.

This is the last piece of my script that I need to iron out before I can put into production.

Here is a copy of my code for your review:

Rich (BB code):
Sub SearchForString()

        Dim LSearchRow As Integer
        Dim LCopyToRow As Integer
        Dim Ctr, LR As Long
        Dim UM7911row
        Dim UM7941row
        Dim NOUM_7941row
        Dim NOUM_7911row
        Dim Voicemail_Migrationrow
        Dim Telecommutersrow
        Dim Confrm_WProw
        Dim Polycomrow
        Dim FAXrow
        Dim OpenArearow
        
        On Error GoTo Err_Execute

        'Define starting past rows
            UM7911row = 2
            UM7941row = 2
            NOUM_7941row = 2
            NOUM_7911row = 2
            Voicemail_Migrationrow = 2
            Telecommutersrow = 2
            Confrm_WProw = 2
            Polycomrow = 2
            FAXrow = 2
            OpenArearow = 2
            
        
    With Worksheets("BLDGMaster")
        
        LR = .Cells(Rows.Count, 15).End(xlUp).Row
            For Ctr = 3 To LR Step 1
                If .Cells(Ctr, 15).Value = "7911" And _
                   .Cells(Ctr, 10).Value = 4450 Then
                    
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("UM7911").Rows(UM7911row)
                    UM7911row = UM7911row + 1
                    
                ElseIf .Cells(Ctr, 15).Value = "7941" And _
                 .Cells(Ctr, 10).Value = 4450 Then
                    
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("UM7941").Rows(UM7941row)
                    UM7941row = UM7941row + 1
                    
                ElseIf .Cells(Ctr, 15).Value = "7941" And _
                 .Cells(Ctr, 10).Value = "" Then
                    
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("NOUM_7941").Rows(NOUM_7941row)
                    NOUM_7941row = NOUM_7941row + 1
                     
                ElseIf .Cells(Ctr, 15).Value = "7911" And _
                 .Cells(Ctr, 10).Value = "" Then
                    
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("NOUM_7911").Rows(NOUM_7911row)
                    NOUM_7911row = NOUM_7911row + 1
                                   
                ElseIf .Cells(Ctr, 10).Value >= 1000 Or _
                 .Cells(Ctr, 10).Value Like "*/*" Then
                                                      
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("Voicemail_Migration").Rows(Voicemail_Migrationrow)
                    Voicemail_Migrationrow = Voicemail_Migrationrow + 1
                    
                                                
                ElseIf .Cells(Ctr, 10).Value Like "*/*" Then
                                                   
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("Telecommuters").Rows(Telecommutersrow)
                    Telecommutersrow = Telecommutersrow + 1
                    
                                                 
                ElseIf .Cells(Ctr, 15).Value = "WP" And _
                 .Cells(Ctr, 9).Value Like "*_*" Then
                                  
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("Confrm_WP").Rows(Confrm_WProw)
                    Confrm_WProw = Confrm_WProw + 1
                    
                ElseIf .Cells(Ctr, 14).Value = "CLEARONE" And _
                 .Cells(Ctr, 15).Value = "CLEARONE" Then
                                  
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("Polycom").Rows(Polycomrow)
                    Polycomrow = Polycomrow + 1
                    
                ElseIf .Cells(Ctr, 14).Value = "2500" And _
                 .Cells(Ctr, 15).Value = "FAX" Then
                                  
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("FAX").Rows(FAXrow)
                    FAXrow = FAXrow + 1
                
                ElseIf .Cells(Ctr, 15).Value = "WP" And _
                 .Cells(Ctr, 9).Value = "Outside" Then
                                  
                   'Select row in BLDGMaster to copy
                    .Rows(Ctr).Copy Destination:=Worksheets("OpenArea").Rows(OpenArearow)
                    OpenArearow = OpenArearow + 1
                              
        End If
            Next Ctr

    End With

        Application.CutCopyMode = False
        MsgBox "All matching data has been copied."
    Exit Sub

Err_Execute: MsgBox "Data Not Found."

End Sub


Thanks for your help with this.

Jim
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
If you use ElseIf, once it meets a single ElseIf condition it will exit out of the If statement, skipping the second. If you want it to copy to both, you should make the query state, "If the pattern looks like this */* then copy it to the telecommuters page" and then nest another If inside that statement saying "If the pattern starts with 1000, etc. then put it in voicemail"

You could really simplify this (I think) by making it with two separate if statements:
Code:
If Like */* Then
    Copy to Telecommuters
End If

If like 1000/* or 1888/* or 1999/* Then
    Copy to Voicemail Migration
End If
Obviously the code as is needs to be tweaked to fit your data, but that way it will check each possibility and copy it into either/both as needed. The If/ElseIf will require nested loops.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top