Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Failing IF Statement | Excel Discussion (Misc queries) | |||
IF statement failing | Excel Programming | |||
"Select" and "Activate" failing? | Excel Programming | |||
Select Method Failing | Excel Programming | |||
select method of range class failing ? | Excel Programming |