if/then problems

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
Greetings, I thought I had a good handle on If/Then's but apparently not. I am using the code below in a userform to look for missing entries in a WS, and everything with the first Msgbox works as it is supposed to. I decided to add in the second Msgbox and now no matter what value is in (i,7) I always get the second Msgbox. I can't figure out what I am missing, I am guessing that "Else" is the wrong one to use? I appreciate any insight - thanks,


VBA Code:
Private Sub RepairButton_Click()

    Dim i As Long
    Dim Lastrow As Long
    
    Worksheets("Device List").Activate
    
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To Lastrow
        If Cells(i, 8).Value = "FAIL" And Cells(i, 7).Value = "" Then
        
        MsgBox "Repair comments required"
                
            RepairForm.Show

     Else
            
        MsgBox "No repairs are needed"
            
        Exit For
      End If
    Next
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Andy B

Active Member
Joined
Mar 26, 2009
Messages
338
Hi, you appear to be exiting the macro as soon as you hit a "no repair" line.
I assume you only wish to exit when you launch the repair form therefor the "Exit For" command should be above the 'Else' not after it.
 

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
603
Office Version
  1. 2019
Platform
  1. Windows
not exactly, but I had tried that already :) I believe that the issue is because of the usage of the "For/Exit For" routine. I think that using Else with that is not appropriate. I rewrote the code to use a reference cell instead and it is working as a typical If/Then statement does.

I appreciate the input.

VBA Code:
Private Sub RepairButton_Click()
    
    Worksheets("Device List").Activate
    
        If Sheets("Coding").Range("H8").Value > 0 Then                                           
            RepairForm.Show            
        Else        
            MsgBox "No further repair comments are needed"
            
    End If
    
End Sub
 

Forum statistics

Threads
1,176,122
Messages
5,901,507
Members
434,898
Latest member
dez091

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
Top