Select statement failing

[DesertCyclist]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

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

 

[Tim Williams]

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]

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

 

[DesertCyclist]

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

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

 

[DesertCyclist]

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

 

[DesertCyclist]

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.

 

[BIG_j]

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