Best way to do if else statement

Handyman84 · Apr 4th 2017

Hi,

I currently have a module in an excel workbook to go to a specific cell date in the header of columns, it also will go to the active row in relation to the matched column header date.

The question is how can the code be condensed seems if an if else statement can be used, how can the code be made more compact?

Code

Sub Button1_Click()
If ActiveCell.Row > 3 Then
'Goto current day in row
For Each Cell In ActiveSheet.Range("$AJ$3:$OI$3")
    If Cell.Value = [Today()] Then
    'Cell.Select
    Cell.Offset(rowOffset:=ActiveCell.Row - 3, columnOffset:=0).Activate
    End If
Next
End If
If ActiveCell.Row < 4 Then
'Goto current day in row
For Each Cell In ActiveSheet.Range("$AJ$3:$OI$3")
    If Cell.Value = [Today()] Then
    Cell.Select
    'Cell.Offset(rowOffset:=ActiveCell.Row - 3, columnOffset:=0).Activate
    End If
Next
End If
End Sub

Display More

Here's my attempt at an if else statement, seems very long for what looks should be less code

Code

Sub Button1_Click()
If ActiveCell.Row > 3 Then
'Goto current day in row
For Each Cell In ActiveSheet.Range("$AJ$3:$OI$3")
    If Cell.Value = [Today()] Then
    'Cell.Select
    Cell.Offset(rowOffset:=ActiveCell.Row - 3, columnOffset:=0).Activate
    End If
Next
Else
If ActiveCell.Row < 4 Then
'Goto current day in row
For Each Cell In ActiveSheet.Range("$AJ$3:$OI$3")
    If Cell.Value = [Today()] Then
    Cell.Select
    'Cell.Offset(rowOffset:=ActiveCell.Row - 3, columnOffset:=0).Activate
    End If
Next
End If
End If


End Sub

Display More

Works but doesn't look right..

**Carim** · Apr 4th 2017

Re: Best way to do if else statement

Hello,

Let's face it ... if your code is working fine ... it's already quite good ...:wink:

Without truly analyzing your code ... may be you should take a look at Select Case ...

Hope this will help

**Ger Plante** · Apr 5th 2017

Re: Best way to do if else statement

its confusing what you are trying to do... by anyway, wouldnt this be better?

Its untested, but you get the idea, right?

Code

Sub Button1_Click() 
    
         'Goto current day in row
        For Each Cell In ActiveSheet.Range("$AJ$3:$OI$3") 
            If Cell.Value = [Today()] Then 
                 'Cell.Select
                If ActiveCell.Row > 3 Then 
                Cell.Offset(rowOffset:=ActiveCell.Row - 3, columnOffset:=0).Activate 
                else
                Cell.Select
            End If 
        Next 
    End If 


End Sub

Display More

By the way, we have a code review forum here just for this kind of thing

Cheers
Ger

**rory** · Apr 5th 2017

Re: Best way to do if else statement

I'd also probably add an Exit For statement.

Best way to do if else statement

Participate now!

Share