Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names in macros - syntax again
I have some macro code that hides a certain range of rows (220 to 228).
However, occasionally, a few rows get added in the middle, which messes up the macro. I think I know that, if I name the range (I did as "net.profit.rows.on.ProjectCF" though the macro keeps changing some of the letters to upper case), and use the range name in the macro, it will adjust for such changes automatically. The first row of code works fine. The second one, my feeble attempt at the aforementioned fix, doesn't. Please tell me how to get this range name to be hidden, in the Else condition. If I need to dimension something too, let me know, please. Thx. Dean If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else Rows("200:228").Hidden = True If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else net.profit.Rows.On.ProjectCF.Hidden = True |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names in macros - syntax again
On Sep 20, 9:03 pm, "Dean" wrote:
I have some macro code that hides a certain range of rows (220 to 228). However, occasionally, a few rows get added in the middle, which messes up the macro. I think I know that, if I name the range (I did as "net.profit.rows.on.ProjectCF" though the macro keeps changing some of the letters to upper case), and use the range name in the macro, it will adjust for such changes automatically. The first row of code works fine. The second one, my feeble attempt at the aforementioned fix, doesn't. Please tell me how to get this range name to be hidden, in the Else condition. If I need to dimension something too, let me know, please. Thx. Dean If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else Rows("200:228").Hidden = True If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else net.profit.Rows.On.ProjectCF.Hidden = True If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else: Range("net_profit").EntireRow.Hidden = True End If |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names in macros - syntax again
Thank you very much! I'm not quite sure why my original one worked (I
think) without the End if. Is it only sometimes necessary? "JW" wrote in message ps.com... On Sep 20, 9:03 pm, "Dean" wrote: I have some macro code that hides a certain range of rows (220 to 228). However, occasionally, a few rows get added in the middle, which messes up the macro. I think I know that, if I name the range (I did as "net.profit.rows.on.ProjectCF" though the macro keeps changing some of the letters to upper case), and use the range name in the macro, it will adjust for such changes automatically. The first row of code works fine. The second one, my feeble attempt at the aforementioned fix, doesn't. Please tell me how to get this range name to be hidden, in the Else condition. If I need to dimension something too, let me know, please. Thx. Dean If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else Rows("200:228").Hidden = True If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else net.profit.Rows.On.ProjectCF.Hidden = True If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else: Range("net_profit").EntireRow.Hidden = True End If |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names in macros - syntax again
it would probably more clear to you if you used the basic if, else, end if
structu Sub test() If a = b Then 'do something ElseIf a = c Then 'do something else Else 'do something different End If End Sub -- Gary "Dean" wrote in message ... Thank you very much! I'm not quite sure why my original one worked (I think) without the End if. Is it only sometimes necessary? "JW" wrote in message ps.com... On Sep 20, 9:03 pm, "Dean" wrote: I have some macro code that hides a certain range of rows (220 to 228). However, occasionally, a few rows get added in the middle, which messes up the macro. I think I know that, if I name the range (I did as "net.profit.rows.on.ProjectCF" though the macro keeps changing some of the letters to upper case), and use the range name in the macro, it will adjust for such changes automatically. The first row of code works fine. The second one, my feeble attempt at the aforementioned fix, doesn't. Please tell me how to get this range name to be hidden, in the Else condition. If I need to dimension something too, let me know, please. Thx. Dean If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else Rows("200:228").Hidden = True If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else net.profit.Rows.On.ProjectCF.Hidden = True If ProfitMode = "Net Profit %" Then Rows("160:208").Hidden = True Else: Range("net_profit").EntireRow.Hidden = True End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names in macros - syntax again
I'm not quite sure why my original one worked (I think) without
the End if. Is it only sometimes necessary? There are two acceptable formats for If-Then statements. If <logical expression Then <Code and If <logical expression Then <Code End If The first form does not require an End If statement, but all your code has to directly follow the Then keyword. Usually, this form is used when there is a single code statement to be executed when the logical expression is true. However, you could place multiple statements following the Then keyword as long as they are separated from each other by a colon (but doing so is not recommended as it makes your code much, much harder to read). The second form is used when there are one or more code statements to be executed when the logical expression is True. You also have to use the second form when there will be ElseIf and/or Else tests. Rick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
range names in macros - syntax again
Thanks much for that info. Also, thanks to Gary.
Dean "Rick Rothstein (MVP - VB)" wrote in message ... I'm not quite sure why my original one worked (I think) without the End if. Is it only sometimes necessary? There are two acceptable formats for If-Then statements. If <logical expression Then <Code and If <logical expression Then <Code End If The first form does not require an End If statement, but all your code has to directly follow the Then keyword. Usually, this form is used when there is a single code statement to be executed when the logical expression is true. However, you could place multiple statements following the Then keyword as long as they are separated from each other by a colon (but doing so is not recommended as it makes your code much, much harder to read). The second form is used when there are one or more code statements to be executed when the logical expression is True. You also have to use the second form when there will be ElseIf and/or Else tests. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
version differences in excel 2007 - use of macros & range names | Excel Discussion (Misc queries) | |||
Range names in macros - DA | Excel Programming | |||
Range names in a Excell macros | Excel Programming | |||
Syntax in Varying Object Names | Excel Programming | |||
Names.Add - Range Syntax Issue | Excel Programming |