Perplexing IF statement

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

Hi Experts,

Why do I know I'm going to be embarrassed by the answer to my query...?

Code takes me to the first empty cell to the right of the E column data.
I don't want to go past column 16 (col. P)
In a test row, i = 18 so there is an if statement to only go to 16 IF i > 15.
However it takes me to column U which is 21.
Works just fine on other rows less than 16 but if i = 16 it goes to 21 also.

Option Explicit

Sub GoRT()
Dim i As Integer

If Not Intersect(ActiveCell, Range("E6:E20")) Is Nothing Then

i = ActiveCell.End(xlToRight).Column

' Msgbox to test i returns 18 on a test row
MsgBox i
' i is > 16 so why does this if statement
' take me to column U which is 21

If i > 16 Then
ActiveCell.Offset(0, 16).Select
Exit Sub
End If

ActiveCell.Offset(0, i - 4).Select
End If
End Sub

Thanks,
Howard
 
Hi Experts,



Why do I know I'm going to be embarrassed by the answer to my query...?



Code takes me to the first empty cell to the right of the E column data.

I don't want to go past column 16 (col. P)

In a test row, i = 18 so there is an if statement to only go to 16 IF i > 15.

However it takes me to column U which is 21.

Works just fine on other rows less than 16 but if i = 16 it goes to 21 also.



Option Explicit



Sub GoRT()

Dim i As Integer



If Not Intersect(ActiveCell, Range("E6:E20")) Is Nothing Then



i = ActiveCell.End(xlToRight).Column



' Msgbox to test i returns 18 on a test row

MsgBox i

' i is > 16 so why does this if statement

' take me to column U which is 21



If i > 16 Then

ActiveCell.Offset(0, 16).Select

Exit Sub

End If



ActiveCell.Offset(0, i - 4).Select

End If

End Sub



Thanks,

Howard
Click to expand...


This is a typo, 16 should be 15
' i is > 16 so why does this if statement
Howard
 
Hi Experts,



Why do I know I'm going to be embarrassed by the answer to my query...?



Code takes me to the first empty cell to the right of the E column data.

I don't want to go past column 16 (col. P)

In a test row, i = 18 so there is an if statement to only go to 16 IF i > 15.

However it takes me to column U which is 21.

Works just fine on other rows less than 16 but if i = 16 it goes to 21 also.



Option Explicit



Sub GoRT()

Dim i As Integer



If Not Intersect(ActiveCell, Range("E6:E20")) Is Nothing Then



i = ActiveCell.End(xlToRight).Column



' Msgbox to test i returns 18 on a test row

MsgBox i

' i is > 16 so why does this if statement

' take me to column U which is 21



If i > 16 Then

ActiveCell.Offset(0, 16).Select

Exit Sub

End If



ActiveCell.Offset(0, i - 4).Select

End If

End Sub



Thanks,

Howard
Click to expand...

Drat!!! Two typos...

"If i > 16 Then" should be 15

Howard
 
Howard,

This code takes you to column 21 because of the line:

ActiveCell.Offset(0, 16).Select

That line will start from the active cell (which is in column E) and go over 16 columns. Since column E is the 5th column, 16 columns over from the active cell is column 21.

To fix, simply change the column offset to:

ActiveCell.Offset(0, 12).Select

Or you could remove the IF statement altogether and rewrite as:

Sub GoRT()
Dim i As Integer

If Not Intersect(ActiveCell, Range("E6:E20")) Is Nothing Then

i = ActiveCell.End(xlToRight).Column
i = WorksheetFunction.Min(i, 16) - 4
MsgBox i
ActiveCell.Offset(0, i).Select
End If
End Sub
 
Hi Experts,



Why do I know I'm going to be embarrassed by the answer to my query...?



Code takes me to the first empty cell to the right of the E column data.

I don't want to go past column 16 (col. P)

In a test row, i = 18 so there is an if statement to only go to 16 IF i > 15.

However it takes me to column U which is 21.

Works just fine on other rows less than 16 but if i = 16 it goes to 21 also.



Option Explicit



Sub GoRT()

Dim i As Integer



If Not Intersect(ActiveCell, Range("E6:E20")) Is Nothing Then



i = ActiveCell.End(xlToRight).Column



' Msgbox to test i returns 18 on a test row

MsgBox i

' i is > 16 so why does this if statement

' take me to column U which is 21



If i > 16 Then

ActiveCell.Offset(0, 16).Select

Exit Sub

End If



ActiveCell.Offset(0, i - 4).Select

End If

End Sub



Thanks,

Howard
Click to expand...

Thanks Ben,

The 16 + 5 = 21 eluded me.

Thanks again.

Howard
 
Back
Top