If else statement-Need help with coding

rsrasc · #1 10-29-2015, 01:57 PM

Just wondering if the below code could be combined or simplified.

Thank you for your assistance and cooperation

Code:

Sub MetIndividualGoal1()

If Range("E5").Value >= Range("F5").Value Then

    Range("H5").Value = "Yes"
    
    Else
    Range("H5").Value = "No"
    
End If

End Sub

Sub MetIndividualGoal2()

If Range("E6").Value >= Range("F6").Value Then

    Range("H6").Value = "Yes"
    
    Else
    Range("H6").Value = "No"
    
End If

End Sub

Sub MetIndividualGoal3()

If Range("E7").Value >= Range("F7").Value Then

    Range("H7").Value = "Yes"
    
    Else
    Range("H7").Value = "No"
    
End If

End Sub

macropod · #2 10-29-2015, 03:02 PM

How about:

Code:

Sub MetIndividualGoals()
Dim i As Long
With ActiveSheet
  For i = 5 To 7
    If .Range("E" & i).Value >= .Range("F" & i).Value Then
      .Range("H" & i).Value = "Yes"
    Else
      .Range("H" & i).Value = "No"
    End If
  Next
End With
End Sub

rsrasc · #3 10-29-2015, 03:46 PM

Clever solution.....

Thanks for your help.

rsrasc · #4 10-30-2015, 12:15 AM

Paul,

I would like to know if you can modify the code to add a second range for "i".

For example, a range from

"For i = 14 To 16"

in addition to the existing one:

"For i = 5 To 7"

I added a separate macro to get it done but would like to know if these two can be combined.

Again, thank you.

macropod · #5 10-30-2015, 01:10 AM

I'd have thought it pretty obvious you could use:

Code:

Sub MetIndividualGoals()
Dim i As Long
With ActiveSheet
  For i = 5 To 7
    If .Range("E" & i).Value >= .Range("F" & i).Value Then
      .Range("H" & i).Value = "Yes"
    Else
      .Range("H" & i).Value = "No"
    End If
  Next
  For i = 14 To 16
    If .Range("E" & i).Value >= .Range("F" & i).Value Then
      .Range("H" & i).Value = "Yes"
    Else
      .Range("H" & i).Value = "No"
    End If
  Next
End With
End Sub

Slightly less obvious is:

Code:

Sub MetIndividualGoals()
Dim i As Long
With ActiveSheet
  For i = 5 To 16
    If (i < 8) Or (i > 13) Then
      If .Range("E" & i).Value >= .Range("F" & i).Value Then
        .Range("H" & i).Value = "Yes"
      Else
        .Range("H" & i).Value = "No"
      End If
    End If
  Next
End With
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
UserForm VBA coding	Niclasfa	Word VBA	1	04-08-2015 10:23 PM
What kind of coding?	ep2002	Excel	3	07-22-2014 07:52 AM
text becomes pages of these *** and then coding	EkkoJohnny	Word	1	11-22-2013 12:37 AM
Coding into a macro a carriage return	sinbad	Word VBA	6	02-27-2012 03:51 AM
Extreme Color Coding Outlook	boodmatt	Outlook	0	06-27-2011 02:13 AM