Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify Code
Hello,
I have the following code to define a range based on month which is sorted date wise. is there a way to avoid repeating the code 12 times for all months? also if there is no dates for a specific month say for november to avoid causing an error while selecting the range since the range will be empty? Thanks Dim iStart As Long Dim iEnd As Long Dim Rng As Range iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngJan" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngFeb" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngMar" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify Code
Dim iStart As Long
Dim iEnd As Long Dim Rng As Range Dim i As Long With Sheets("Daily") For i = 1 To 12 iStart = _ .Evaluate("=MIN(IF(MONTH(DlyAll)=" & i & ",ROW(DlyAll)))") iEnd = _ .Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rng" & Format(DateValue("01-" & i), "mmm") Next i End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Soniya" wrote in message oups.com... Hello, I have the following code to define a range based on month which is sorted date wise. is there a way to avoid repeating the code 12 times for all months? also if there is no dates for a specific month say for november to avoid causing an error while selecting the range since the range will be empty? Thanks Dim iStart As Long Dim iEnd As Long Dim Rng As Range iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngJan" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngFeb" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngMar" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify Code
Thanks for your help.
Unfortunately it is not working for me ???? to avoid the error of having empty range i put on error resume next Bob Phillips wrote: Dim iStart As Long Dim iEnd As Long Dim Rng As Range Dim i As Long With Sheets("Daily") For i = 1 To 12 iStart = _ .Evaluate("=MIN(IF(MONTH(DlyAll)=" & i & ",ROW(DlyAll)))") iEnd = _ .Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rng" & Format(DateValue("01-" & i), "mmm") Next i End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Soniya" wrote in message oups.com... Hello, I have the following code to define a range based on month which is sorted date wise. is there a way to avoid repeating the code 12 times for all months? also if there is no dates for a specific month say for november to avoid causing an error while selecting the range since the range will be empty? Thanks Dim iStart As Long Dim iEnd As Long Dim Rng As Range iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngJan" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngFeb" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngMar" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify Code
In what way?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Soniya" wrote in message oups.com... Thanks for your help. Unfortunately it is not working for me ???? to avoid the error of having empty range i put on error resume next Bob Phillips wrote: Dim iStart As Long Dim iEnd As Long Dim Rng As Range Dim i As Long With Sheets("Daily") For i = 1 To 12 iStart = _ .Evaluate("=MIN(IF(MONTH(DlyAll)=" & i & ",ROW(DlyAll)))") iEnd = _ .Evaluate("=MAX(IF(MONTH(DlyAll)=1,ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rng" & Format(DateValue("01-" & i), "mmm") Next i End With -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Soniya" wrote in message oups.com... Hello, I have the following code to define a range based on month which is sorted date wise. is there a way to avoid repeating the code 12 times for all months? also if there is no dates for a specific month say for november to avoid causing an error while selecting the range since the range will be empty? Thanks Dim iStart As Long Dim iEnd As Long Dim Rng As Range iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=1, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngJan" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=2, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngFeb" iStart = _ Sheets("Daily").Evaluate("=MIN(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") iEnd = Sheets("Daily").Evaluate("=MAX(IF(MONTH(DlyAll)=3, ROW(DlyAll)))") Set Rng = Range("A" & iStart & ":A" & iEnd) Rng.Name = "rngMar" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I simplify a VB code?????? | Excel Discussion (Misc queries) | |||
Simplify code | Excel Worksheet Functions | |||
Need to simplify code | Excel Discussion (Misc queries) | |||
Simplify this code | Excel Programming | |||
simplify code | Excel Discussion (Misc queries) |