Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hello
I am writing this code (below) and a window appears with Compile error and Syntax error. I am having also this part of the code highlighted. Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), This is the code: Option Explicit Dim nextSecond Sub startFlashing() flashCell End Sub Sub stopFlashing() On Error Resume Next Application.OnTime nextSecond, "flashCell", , False End Sub Sub flashCell() nextSecond = Now + TimeValue("00:00:01") Application.OnTime nextSecond, "flashCell" If Range("A105").Interior.ColorIndex =4 Then Range("A105").Interior.ColorIndex = 6 Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), Range("H102"))) ElseIf Range("A105").Interior.ColorIndex = 6 Then Range("A105").Interior.ColorIndex = 4 Range("A105").Value = "WORLD CHAMPIONS 2010" End If End Sub I appriciate even a small help. Thanks in advance. |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
"MAX" wrote:
I am writing this code (below) and a window appears with Compile error and Syntax error. I suspect that your error is due to an explicit line break without the necessary continuation character. But it is difficult to say for sure since it is difficult to discern which line breaks in your posting are due to text wrapping in the message v. what you actually entered in the VBA enter. In summary, VBA statements must be a "single" line. Long lines can be continued by putting an underscore (_) at the end, but only after a space; ergo, only where a space is valid in the statement. The following should work "for sure", keeping lines short enough to avoid line wrapping in the message (I hope): Range("A105").Value = _ IIf(Range("F102") = Range("G102"), Range("A99"), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) ----- original message ----- "MAX" wrote: Hello I am writing this code (below) and a window appears with Compile error and Syntax error. I am having also this part of the code highlighted. Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), This is the code: Option Explicit Dim nextSecond Sub startFlashing() flashCell End Sub Sub stopFlashing() On Error Resume Next Application.OnTime nextSecond, "flashCell", , False End Sub Sub flashCell() nextSecond = Now + TimeValue("00:00:01") Application.OnTime nextSecond, "flashCell" If Range("A105").Interior.ColorIndex =4 Then Range("A105").Interior.ColorIndex = 6 Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), Range("H102"))) ElseIf Range("A105").Interior.ColorIndex = 6 Then Range("A105").Interior.ColorIndex = 4 Range("A105").Value = "WORLD CHAMPIONS 2010" End If End Sub I appriciate even a small help. Thanks in advance. |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Thanks Joe it worked but now I need to add another Elself statement which is:
If cells F102 and G102 remain equal, then Range("A105").Value = Range(J102)Range(K102),Range(E102),Range(H102). Will you please tell me how to write this statement and where I have to insert it. Again thank you so much. "Joe User" wrote: "MAX" wrote: I am writing this code (below) and a window appears with Compile error and Syntax error. I suspect that your error is due to an explicit line break without the necessary continuation character. But it is difficult to say for sure since it is difficult to discern which line breaks in your posting are due to text wrapping in the message v. what you actually entered in the VBA enter. In summary, VBA statements must be a "single" line. Long lines can be continued by putting an underscore (_) at the end, but only after a space; ergo, only where a space is valid in the statement. The following should work "for sure", keeping lines short enough to avoid line wrapping in the message (I hope): Range("A105").Value = _ IIf(Range("F102") = Range("G102"), Range("A99"), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) ----- original message ----- "MAX" wrote: Hello I am writing this code (below) and a window appears with Compile error and Syntax error. I am having also this part of the code highlighted. Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), This is the code: Option Explicit Dim nextSecond Sub startFlashing() flashCell End Sub Sub stopFlashing() On Error Resume Next Application.OnTime nextSecond, "flashCell", , False End Sub Sub flashCell() nextSecond = Now + TimeValue("00:00:01") Application.OnTime nextSecond, "flashCell" If Range("A105").Interior.ColorIndex =4 Then Range("A105").Interior.ColorIndex = 6 Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), Range("H102"))) ElseIf Range("A105").Interior.ColorIndex = 6 Then Range("A105").Interior.ColorIndex = 4 Range("A105").Value = "WORLD CHAMPIONS 2010" End If End Sub I appriciate even a small help. Thanks in advance. |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
"MAX" wrote:
now I need to add another Elself statement which is: If cells F102 and G102 remain equal, then Range("A105").Value = Range(J102)Range(K102),Range(E102),Range(H102) Your requirement is unclear because in your original posting, the result was Range("A99") if Range("F102")=Range("G102"). So instead of "another ElseIf", perhaps you want: Range("A105").Value = _ IIf(Range("F102") = Range("G102"), _ IIf(Range("J102") Range("K102"), Range("E102"), _ Range("H102")), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) Of course, I would indent that differently for readability. But I have had trouble with the appearance of indented text in postings. So I avoid it :-(. Also, I hasten to point out that unlike some computer languages, VBA fully evaluates the entire expression, even if the truth value of some subexpressions would obviate the need to evaluate other subexpressions. So for example, if it would be incorrect to compare Range("J102")Range("K102") when Range("F102")Range("G102"), it would behoove you to restructure the code. For example: 'better to declare the type of x; 'but I cannot infer that from your code fragment Dim x If Range("F102") = Range("G102") Then x = IIf(Range("J102") Range("K102"), Range("E102"), _ Range("H102")) Else x = IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) End If Range("A105").Value = x ----- original message ----- "MAX" wrote: Thanks Joe it worked but now I need to add another Elself statement which is: If cells F102 and G102 remain equal, then Range("A105").Value = Range(J102)Range(K102),Range(E102),Range(H102). Will you please tell me how to write this statement and where I have to insert it. Again thank you so much. "Joe User" wrote: "MAX" wrote: I am writing this code (below) and a window appears with Compile error and Syntax error. I suspect that your error is due to an explicit line break without the necessary continuation character. But it is difficult to say for sure since it is difficult to discern which line breaks in your posting are due to text wrapping in the message v. what you actually entered in the VBA enter. In summary, VBA statements must be a "single" line. Long lines can be continued by putting an underscore (_) at the end, but only after a space; ergo, only where a space is valid in the statement. The following should work "for sure", keeping lines short enough to avoid line wrapping in the message (I hope): Range("A105").Value = _ IIf(Range("F102") = Range("G102"), Range("A99"), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) ----- original message ----- "MAX" wrote: Hello I am writing this code (below) and a window appears with Compile error and Syntax error. I am having also this part of the code highlighted. Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), This is the code: Option Explicit Dim nextSecond Sub startFlashing() flashCell End Sub Sub stopFlashing() On Error Resume Next Application.OnTime nextSecond, "flashCell", , False End Sub Sub flashCell() nextSecond = Now + TimeValue("00:00:01") Application.OnTime nextSecond, "flashCell" If Range("A105").Interior.ColorIndex =4 Then Range("A105").Interior.ColorIndex = 6 Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), Range("H102"))) ElseIf Range("A105").Interior.ColorIndex = 6 Then Range("A105").Interior.ColorIndex = 4 Range("A105").Value = "WORLD CHAMPIONS 2010" End If End Sub I appriciate even a small help. Thanks in advance. |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Joe sorry for any inconvenience, shall I send you the file?
Thanks "Joe User" wrote: "MAX" wrote: now I need to add another Elself statement which is: If cells F102 and G102 remain equal, then Range("A105").Value = Range(J102)Range(K102),Range(E102),Range(H102) Your requirement is unclear because in your original posting, the result was Range("A99") if Range("F102")=Range("G102"). So instead of "another ElseIf", perhaps you want: Range("A105").Value = _ IIf(Range("F102") = Range("G102"), _ IIf(Range("J102") Range("K102"), Range("E102"), _ Range("H102")), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) Of course, I would indent that differently for readability. But I have had trouble with the appearance of indented text in postings. So I avoid it :-(. Also, I hasten to point out that unlike some computer languages, VBA fully evaluates the entire expression, even if the truth value of some subexpressions would obviate the need to evaluate other subexpressions. So for example, if it would be incorrect to compare Range("J102")Range("K102") when Range("F102")Range("G102"), it would behoove you to restructure the code. For example: 'better to declare the type of x; 'but I cannot infer that from your code fragment Dim x If Range("F102") = Range("G102") Then x = IIf(Range("J102") Range("K102"), Range("E102"), _ Range("H102")) Else x = IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) End If Range("A105").Value = x ----- original message ----- "MAX" wrote: Thanks Joe it worked but now I need to add another Elself statement which is: If cells F102 and G102 remain equal, then Range("A105").Value = Range(J102)Range(K102),Range(E102),Range(H102). Will you please tell me how to write this statement and where I have to insert it. Again thank you so much. "Joe User" wrote: "MAX" wrote: I am writing this code (below) and a window appears with Compile error and Syntax error. I suspect that your error is due to an explicit line break without the necessary continuation character. But it is difficult to say for sure since it is difficult to discern which line breaks in your posting are due to text wrapping in the message v. what you actually entered in the VBA enter. In summary, VBA statements must be a "single" line. Long lines can be continued by putting an underscore (_) at the end, but only after a space; ergo, only where a space is valid in the statement. The following should work "for sure", keeping lines short enough to avoid line wrapping in the message (I hope): Range("A105").Value = _ IIf(Range("F102") = Range("G102"), Range("A99"), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) ----- original message ----- "MAX" wrote: Hello I am writing this code (below) and a window appears with Compile error and Syntax error. I am having also this part of the code highlighted. Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), This is the code: Option Explicit Dim nextSecond Sub startFlashing() flashCell End Sub Sub stopFlashing() On Error Resume Next Application.OnTime nextSecond, "flashCell", , False End Sub Sub flashCell() nextSecond = Now + TimeValue("00:00:01") Application.OnTime nextSecond, "flashCell" If Range("A105").Interior.ColorIndex =4 Then Range("A105").Interior.ColorIndex = 6 Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), Range("H102"))) ElseIf Range("A105").Interior.ColorIndex = 6 Then Range("A105").Interior.ColorIndex = 4 Range("A105").Value = "WORLD CHAMPIONS 2010" End If End Sub I appriciate even a small help. Thanks in advance. |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
"MAX" wrote:
Joe sorry for any inconvenience, shall I send you the file? I 'spose you could. Send it to joeu2004 "at" hotmail.com. But it's not obvious to me that that would be any better than your simply posting the code fragment here, written as well as you can with syntax errors. ----- original message ----- "MAX" wrote: Joe sorry for any inconvenience, shall I send you the file? Thanks "Joe User" wrote: "MAX" wrote: now I need to add another Elself statement which is: If cells F102 and G102 remain equal, then Range("A105").Value = Range(J102)Range(K102),Range(E102),Range(H102) Your requirement is unclear because in your original posting, the result was Range("A99") if Range("F102")=Range("G102"). So instead of "another ElseIf", perhaps you want: Range("A105").Value = _ IIf(Range("F102") = Range("G102"), _ IIf(Range("J102") Range("K102"), Range("E102"), _ Range("H102")), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) Of course, I would indent that differently for readability. But I have had trouble with the appearance of indented text in postings. So I avoid it :-(. Also, I hasten to point out that unlike some computer languages, VBA fully evaluates the entire expression, even if the truth value of some subexpressions would obviate the need to evaluate other subexpressions. So for example, if it would be incorrect to compare Range("J102")Range("K102") when Range("F102")Range("G102"), it would behoove you to restructure the code. For example: 'better to declare the type of x; 'but I cannot infer that from your code fragment Dim x If Range("F102") = Range("G102") Then x = IIf(Range("J102") Range("K102"), Range("E102"), _ Range("H102")) Else x = IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) End If Range("A105").Value = x ----- original message ----- "MAX" wrote: Thanks Joe it worked but now I need to add another Elself statement which is: If cells F102 and G102 remain equal, then Range("A105").Value = Range(J102)Range(K102),Range(E102),Range(H102). Will you please tell me how to write this statement and where I have to insert it. Again thank you so much. "Joe User" wrote: "MAX" wrote: I am writing this code (below) and a window appears with Compile error and Syntax error. I suspect that your error is due to an explicit line break without the necessary continuation character. But it is difficult to say for sure since it is difficult to discern which line breaks in your posting are due to text wrapping in the message v. what you actually entered in the VBA enter. In summary, VBA statements must be a "single" line. Long lines can be continued by putting an underscore (_) at the end, but only after a space; ergo, only where a space is valid in the statement. The following should work "for sure", keeping lines short enough to avoid line wrapping in the message (I hope): Range("A105").Value = _ IIf(Range("F102") = Range("G102"), Range("A99"), _ IIf(Range("F102") Range("G102"), Range("E102"), _ Range("H102"))) ----- original message ----- "MAX" wrote: Hello I am writing this code (below) and a window appears with Compile error and Syntax error. I am having also this part of the code highlighted. Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), This is the code: Option Explicit Dim nextSecond Sub startFlashing() flashCell End Sub Sub stopFlashing() On Error Resume Next Application.OnTime nextSecond, "flashCell", , False End Sub Sub flashCell() nextSecond = Now + TimeValue("00:00:01") Application.OnTime nextSecond, "flashCell" If Range("A105").Interior.ColorIndex =4 Then Range("A105").Interior.ColorIndex = 6 Range("A105").Value = IIf(Range("F102") = Range("G102"), Range("A99"), IIf(Range("F102") Range("G102"), Range("E102"), Range("H102"))) ElseIf Range("A105").Interior.ColorIndex = 6 Then Range("A105").Interior.ColorIndex = 4 Range("A105").Value = "WORLD CHAMPIONS 2010" End If End Sub I appriciate even a small help. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Bubble sort Error (Compile Error: Type Mismtach) | Excel Programming | |||
VBAProject name compile error, not defined at compile time | Excel Programming | |||
error message: compile error, argument not optional | Excel Programming | |||
How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) |