Select statement failing
 

Can anyone please tell me what might lead Excel to ignore these statements:

Worksheets(€œLog€�).Select
Range(€œE4€�).Select
ActiveCell.Offset(1,0).Select

There is a €œLog€� worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. Ive never encountered a situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that the
function takes as parameters (changes to which cause the function to execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

Your outer loop needs a condition to stop it. Right now it is endless.
Do until ? loop until ?

"DesertCyclist" wrote:

Can anyone please tell me what might lead Excel to ignore these statements:

Worksheets(€œLog€�).Select
Range(€œE4€�).Select
ActiveCell.Offset(1,0).Select

There is a €œLog€� worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. Ive never encountered a situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that the
function takes as parameters (changes to which cause the function to execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

P.S. I didn't have any problem with the select

"DesertCyclist" wrote:

Can anyone please tell me what might lead Excel to ignore these statements:

Worksheets(€œLog€�).Select
Range(€œE4€�).Select
ActiveCell.Offset(1,0).Select

There is a €œLog€� worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. Ive never encountered a situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that the
function takes as parameters (changes to which cause the function to execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

A function called from a worksheet cell formula can't change the Excel
environment (eg. format cells etc).
Presumably that also extends to selecting sheets/cells.

However, your formula doesn't need any of those Select's.


Tim


"DesertCyclist" wrote in message
...
Can anyone please tell me what might lead Excel to ignore these
statements:

Worksheets("Log").Select
Range("E4").Select
ActiveCell.Offset(1,0).Select

There is a "Log" worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. I've never encountered a
situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that
the
function takes as parameters (changes to which cause the function to
execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat",
ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

Thanks, but that doesn't explain why it's not processing the selects.

"JLGWhiz" wrote:

Your outer loop needs a condition to stop it. Right now it is endless.
Do until ? loop until ?

"DesertCyclist" wrote:

Can anyone please tell me what might lead Excel to ignore these statements:

Worksheets(€œLog€�).Select
Range(€œE4€�).Select
ActiveCell.Offset(1,0).Select

There is a €œLog€� worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. Ive never encountered a situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that the
function takes as parameters (changes to which cause the function to execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

I wonder why *I* am having a problem with it.

"JLGWhiz" wrote:

P.S. I didn't have any problem with the select

"DesertCyclist" wrote:

Can anyone please tell me what might lead Excel to ignore these statements:

Worksheets(€œLog€�).Select
Range(€œE4€�).Select
ActiveCell.Offset(1,0).Select

There is a €œLog€� worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. Ive never encountered a situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that the
function takes as parameters (changes to which cause the function to execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

The function is only reading values and not changing anything. It doesn't
need the selets? How so?

"Tim Williams" wrote:

A function called from a worksheet cell formula can't change the Excel
environment (eg. format cells etc).
Presumably that also extends to selecting sheets/cells.

However, your formula doesn't need any of those Select's.


Tim


"DesertCyclist" wrote in message
...
Can anyone please tell me what might lead Excel to ignore these
statements:

Worksheets("Log").Select
Range("E4").Select
ActiveCell.Offset(1,0).Select

There is a "Log" worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. I've never encountered a
situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that
the
function takes as parameters (changes to which cause the function to
execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat",
ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

Oh, I think I see what you mean. Instead of the selects, I can just "examine"
the contents of the cells. That sounds promising. I'll try that. Thank you.

"DesertCyclist" wrote:

The function is only reading values and not changing anything. It doesn't
need the selets? How so?

"Tim Williams" wrote:

A function called from a worksheet cell formula can't change the Excel
environment (eg. format cells etc).
Presumably that also extends to selecting sheets/cells.

However, your formula doesn't need any of those Select's.


Tim


"DesertCyclist" wrote in message
...
Can anyone please tell me what might lead Excel to ignore these
statements:

Worksheets("Log").Select
Range("E4").Select
ActiveCell.Offset(1,0).Select

There is a "Log" worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. I've never encountered a
situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that
the
function takes as parameters (changes to which cause the function to
execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat",
ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you

Select statement failing
 

BTW,

You need to make the sheet "active" first. So you would do...

Worksheets(€œLog€�).Activate
Worksheets(€œLog€�).Select

or

ActiveSheet.Range(€œE4€�).Select

ActiveCell.Offset(1,0).Select


"DesertCyclist" wrote in message ...
Can anyone please tell me what might lead Excel to ignore these statements:

Worksheets(€œLog€�).Select
Range(€œE4€�).Select
ActiveCell.Offset(1,0).Select

There is a €œLog€� worksheet, there is a cell E4 in it, and there is a cell
below it. These statements are so basic. Ive never encountered a situation
in which Excel would ignore them, so I am at a loss to know where to even
start looking.

Here are some hopefully-relevant facts:

These statements are in a function in a module.
The function is called from a cell.
The function is executing (it hits a breakpoint).
The cell containing the function call is not in the range of cells that the
function takes as parameters (changes to which cause the function to execute).
The range of cells through which it is looping is the same as the range of
cells that the function takes as parameters (changes to which cause the
function to execute).

Here is the function:

Public Function CurrentWeight() As Single

Dim CurrentSheetName As String

CurrentSheetName = ActiveSheet.Name

Worksheets("Log").Select
Range("E4").Select
Do

If InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value < "" Then
CurrentWeight = ActiveCell.Value
End If

Do
ActiveCell.Offset(1, 0).Select
Loop Until InStr("Sun Mon Tue Wed Thu Fri Sat", ActiveCell.Offset(0,
-2).Value) 0 And ActiveCell.Value = ""

Loop

Worksheets(CurrentSheetName).Select

End Function


Any help would be appreciated.

Thank you