VBA line of code executes in Immediate Window but not in Code Window
 

2003/2007

Using 2007

Dim StartSearch as Long (Value preceeding line below is 13)
Dim FormulaStr as String (Value preceeding/after line below is 123]
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


StartSearch = IIf(InStr(StartSearch, FormulaStr, "'!") = InStr(StartSearch, FormulaStr, "'"), _
InStr(StartSearch, FormulaStr, "'!") + 2, InStr(StartSearch, FormulaStr, "'") + 1)

In the Code Window, this line does not change the next StartSearch value from 13 to 46.
Also, no error message is displayed.

But if I copy/paste/execute the same line in the Immediate Window StartSearch chages
from 13 to 46 as it should.

What am I missing? or is there a Bug in 2007?

TIA EagleOne

VBA line of code executes in Immediate Window but not in Code Window
 

There may well be a bug in Excel xl 2007

Just on a hunch, I Copy/Pasted a second line of the same code under the first line and it worked.
If I delete either the first or second line neither works?

Also, I retyped the line of VBA code in case there was "garbage"; this did not work

This is the first time I can remember having to Code two lines of VBA Code to get one to work.

Any thoughts?

EagleOne

wrote:

2003/2007

Using 2007

Dim StartSearch as Long (Value preceeding line below is 13)
Dim FormulaStr as String (Value preceeding/after line below is 123]
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


StartSearch = IIf(InStr(StartSearch, FormulaStr, "'!") = InStr(StartSearch, FormulaStr, "'"), _
InStr(StartSearch, FormulaStr, "'!") + 2, InStr(StartSearch, FormulaStr, "'") + 1)

In the Code Window, this line does not change the next StartSearch value from 13 to 46.
Also, no error message is displayed.

But if I copy/paste/execute the same line in the Immediate Window StartSearch chages
from 13 to 46 as it should.

What am I missing? or is there a Bug in 2007?

TIA EagleOne

VBA line of code executes in Immediate Window but not in Code Window
 

Hi,
Do you have a "sub..." /End sub?

--
John
johnf202 at hot mail dot com


wrote in message
...
2003/2007

Using 2007

Dim StartSearch as Long (Value preceeding line below is 13)
Dim FormulaStr as String (Value preceeding/after line below is 123]
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


StartSearch = IIf(InStr(StartSearch, FormulaStr, "'!") =
InStr(StartSearch, FormulaStr, "'"), _
InStr(StartSearch, FormulaStr, "'!") + 2, InStr(StartSearch, FormulaStr,
"'") + 1)

In the Code Window, this line does not change the next StartSearch value
from 13 to 46.
Also, no error message is displayed.

But if I copy/paste/execute the same line in the Immediate Window
StartSearch chages
from 13 to 46 as it should.

What am I missing? or is there a Bug in 2007?

TIA EagleOne