| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and "If"?!
I have this code runs fine but, for Zone4=7, its row gets hidden
although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and "If"?!
I can see that you have a named range called Zone4 but it also appears that
you have a variable called Zone4. Where is your variable Zone4 declared and how is the value initialized? I guess what I am saying is post the rest of your code... -- HTH... Jim Thomlinson "Jo" wrote: I have this code runs fine but, for Zone4=7, its row gets hidden although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and "If"?!
On Aug 10, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: I can see that you have a named range called Zone4 but it also appears that you have a variable called Zone4. Where is your variable Zone4 declared and how is the value initialized? I guess what I am saying is post the rest of your code... -- HTH... Jim Thomlinson "Jo" wrote: I have this code runs fine but, for Zone4=7, its row gets hidden although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If- Hide quoted text - - Show quoted text - Zone5, ...Zone2 are cell names only and I have them nowhere defined in the code. Simply I am telling the code this: where Zone5=0, hide the whole row, same to Zone4,..Zone2! How I can fix this? Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and "If"?!
On Aug 10, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: I can see that you have a named range called Zone4 but it also appears that you have a variable called Zone4. Where is your variable Zone4 declared and how is the value initialized? I guess what I am saying is post the rest of your code... -- HTH... Jim Thomlinson "Jo" wrote: I have this code runs fine but, for Zone4=7, its row gets hidden although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If- Hide quoted text - - Show quoted text - Whole code is below: Sub ZeroPointChangeA() ' ' ZeroPointChange Macro ' Macro recorded 8/2/2007 by Michael Sultan ' ' ActiveSheet.ChartObjects("Chart 46").Activate ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = Names("BaseCPM1").RefersToRange.Value .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With ActiveChart.PlotArea.Select ActiveChart.Axes(xlCategory).Select With ActiveChart.Axes(xlCategory) .MinimumScale = Names("BaseCR1").RefersToRange.Value * 100 .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With If Zone5 <= 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 <= 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 <= 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 <= 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and "If"?!
So Zone5, Zone4 and ... are only named ranges. In that case you can not just
reference them as if they were variables. Give this a try... If Range("Zone5").Value = 0 Then Range("Zone5").EntireRow.Hidden = True End If If Range("Zone4").Value = 0 Then Range("Zone4").EntireRow.Hidden = True End If If Range("Zone3").Value = 0 Then Range("Zone3").EntireRow.Hidden = True End If If Range("Zone2").Value = 0 Then Range("Zone2").EntireRow.Hidden = True End If -- HTH... Jim Thomlinson "Jo" wrote: On Aug 10, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: I can see that you have a named range called Zone4 but it also appears that you have a variable called Zone4. Where is your variable Zone4 declared and how is the value initialized? I guess what I am saying is post the rest of your code... -- HTH... Jim Thomlinson "Jo" wrote: I have this code runs fine but, for Zone4=7, its row gets hidden although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If- Hide quoted text - - Show quoted text - Whole code is below: Sub ZeroPointChangeA() ' ' ZeroPointChange Macro ' Macro recorded 8/2/2007 by Michael Sultan ' ' ActiveSheet.ChartObjects("Chart 46").Activate ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = Names("BaseCPM1").RefersToRange.Value .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With ActiveChart.PlotArea.Select ActiveChart.Axes(xlCategory).Select With ActiveChart.Axes(xlCategory) .MinimumScale = Names("BaseCR1").RefersToRange.Value * 100 .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With If Zone5 <= 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 <= 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 <= 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 <= 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and "If"?!
Just as an observation, the OP could probably shorten these if blocks to:
For i = 2 to 5 With Range("Zone" & i) .EntireRow.Hidden = (.Value = 0) End With Next i but the OP has used both <= 0 and = 0 as the condition for hiding the row(s) and I'm assuming the named ranges are single cells. Otherwise would probably have to loop through the range and hide/unhide every row. "Jim Thomlinson" wrote: So Zone5, Zone4 and ... are only named ranges. In that case you can not just reference them as if they were variables. Give this a try... If Range("Zone5").Value = 0 Then Range("Zone5").EntireRow.Hidden = True End If If Range("Zone4").Value = 0 Then Range("Zone4").EntireRow.Hidden = True End If If Range("Zone3").Value = 0 Then Range("Zone3").EntireRow.Hidden = True End If If Range("Zone2").Value = 0 Then Range("Zone2").EntireRow.Hidden = True End If -- HTH... Jim Thomlinson "Jo" wrote: On Aug 10, 12:48 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: I can see that you have a named range called Zone4 but it also appears that you have a variable called Zone4. Where is your variable Zone4 declared and how is the value initialized? I guess what I am saying is post the rest of your code... -- HTH... Jim Thomlinson "Jo" wrote: I have this code runs fine but, for Zone4=7, its row gets hidden although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If- Hide quoted text - - Show quoted text - Whole code is below: Sub ZeroPointChangeA() ' ' ZeroPointChange Macro ' Macro recorded 8/2/2007 by Michael Sultan ' ' ActiveSheet.ChartObjects("Chart 46").Activate ActiveChart.Axes(xlValue).Select With ActiveChart.Axes(xlValue) .MinimumScale = Names("BaseCPM1").RefersToRange.Value .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With ActiveChart.PlotArea.Select ActiveChart.Axes(xlCategory).Select With ActiveChart.Axes(xlCategory) .MinimumScale = Names("BaseCR1").RefersToRange.Value * 100 .MaximumScaleIsAuto = True .MinorUnitIsAuto = True .MajorUnitIsAuto = True .Crosses = xlAutomatic .ReversePlotOrder = False .ScaleType = xlLinear .DisplayUnit = xlNone End With If Zone5 <= 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 <= 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 <= 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 <= 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and "If"?!
Are all 4 Named Ranges on different rows? No overlaps? (Have you
doublechecked?) My guess would be that either Zone5, 3 or 2 is on the same row as (or has a range that overlaps) Zone4 and that one of them equals zero, causing Zone 4 to be hidden as well. HTH, "Jo" wrote in message ups.com... I have this code runs fine but, for Zone4=7, its row gets hidden although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA and "If"?!
On Aug 10, 12:51 pm, "George Nicholson"
wrote: Are all 4 Named Ranges on different rows? No overlaps? (Have you doublechecked?) My guess would be that either Zone5, 3 or 2 is on the same row as (or has a range that overlaps) Zone4 and that one of them equals zero, causing Zone 4 to be hidden as well. HTH, "Jo" wrote in message ups.com... I have this code runs fine but, for Zone4=7, its row gets hidden although it shouldn't according to the code below. What could I be missing? If Zone5 = 0 Then Range("Zone5").Select Selection.EntireRow.Hidden = True End If If Zone4 = 0 Then Range("Zone4").Select Selection.EntireRow.Hidden = True End If If Zone3 = 0 Then Range("Zone3").Select Selection.EntireRow.Hidden = True End If If Zone2 = 0 Then Range("Zone2").Select Selection.EntireRow.Hidden = True End If- Hide quoted text - - Show quoted text - George, No overlapping for sure! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
![Jo[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
