PDA

View Full Version : Dynamic Range Help



c7015
02-24-2012, 09:32 AM
I'm trying to make a dynamic range based on checked boxes that spans columns c e f g h

ElseIf optionbutton1.value = True And optionbutton3.value = True And optionbutton4.value = True And Optionbutton5.value = True And optionbutton6.value = True Then
Dim DynamicRange As Range

ActiveSheet.Select
Range("C20,E20,F20,G20,H20").Select
Range("H20").Activate
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select


ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=DynamicRange

but for some reason it will only select the C column and notthe rest (e f g h ):banghead:

what am I doing wrong here ?

p45cal
02-24-2012, 10:28 AM
maybe you're after doing something like:
Set DynamicRange = Range(Range("H20"), Range("H20").End(xlDown))
Set DynamicRange = Intersect(DynamicRange.EntireRow, Range("C:C,E:H"))
DynamicRange.Select
Decide which column you want the depth of the range to depend on (H above) and adjust accordingly.

c7015
02-27-2012, 07:52 AM
Thanks P45

I actually got it going like this

ElseIf optionbutton2.value = True And optionbutton3.value = True And optionbutton4.value = True And Optionbutton5.value = True And optionbutton6.value = True Then

lRw = WorksheetFunction.Max([d20].End(xlDown).Row, [e20:h20].End(xlDown).Row)
Set dRng = Union(Range("d20", "d" & lRw), Range("E20:H" & lRw))
dRng.Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=dRng

With Sheets("summary")
lRw = WorksheetFunction.Max(.Range("d27").End(xlDown).Row, .Range("e27:h27").End(xlDown).Row)
Set dRng = Union(.Range("d27", "d" & lRw), .Range("E27:H" & lRw))
dRng.Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=dRng
End With

but it wont seem to take my second chart from the non active page , giving me a select method of range class failed , debugging on the 2nd dRng.select line ??

p45cal
02-27-2012, 08:33 AM
but it wont seem to take my second chart from the non active page , giving me a select method of range class failed , debugging on the 2nd dRng.select line ??Probably because the sheet summary is not the active sheet.
You probably don't need that select line at all - try it commented out.

This is unusually convoluted!:
Set dRng = Union(Range("d20", "d" & lRw), Range("E20:H" & lRw))

It is the same as:
Set dRng = Range("D20:H" & lRw)

Even if you wanted non-contiguous ranges as implied in you first post (you used C instead of D) then
Set dRng = Union(Range("c20", "c" & lRw), Range("E20:H" & lRw))
can be simplified a bit to:
Set dRng = Union(Range("c20:c" & lRw), Range("E20:H" & lRw))

c7015
02-28-2012, 01:15 PM
Probably because the sheet summary is not the active sheet.
You probably don't need that select line at all - try it commented out.

This is unusually convoluted!:
Set dRng = Union(Range("d20", "d" & lRw), Range("E20:H" & lRw))

It is the same as:
Set dRng = Range("D20:H" & lRw)

Even if you wanted non-contiguous ranges as implied in you first post (you used C instead of D) then
Set dRng = Union(Range("c20", "c" & lRw), Range("E20:H" & lRw))
can be simplified a bit to:
Set dRng = Union(Range("c20:c" & lRw), Range("E20:H" & lRw))

Thanks P45 Your on the money , I took it out and it works much better ..


perhaps you can help me with another issue I am having with my same macro


Ok so I have a macro that checks for an age in column a , if the age matches the macro will offset to the specific value and clear everything under the age

Set findit = Range("A21:A130").Find(what:=rbox1.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 2).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A21:A130").Find(what:=t20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A21:A130").Find(what:=nt10ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 4).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A21:A130").Find(what:=nt20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

and that works fine for what I want , however

now I want to switch sheets and do the same thing on a sheet called "summary" with the range A28:A127

so I copy the whole code but change the range and add it after what I have (as well as tell it to change sheets)

Sheets("summary").Select
cells(28, 1).Value = Age.Value + 1
Selection.AutoFill Destination:=Range("A28:A127"), Type:=xlFillSeries
Range("A28:A127").Select

Set findit = Range("A28:A127").Find(what:=rbox1.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 2).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A28:A127").Find(what:=t20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A28:A127").Find(what:=nt10ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 4).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

Set findit = Range("A28:A127").Find(what:=nt20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

End Sub

but it must be too many find it cases because it crashes my macro ...
how can I do this better ?


thanks

p45cal
02-28-2012, 02:33 PM
on which line does it crash?

c7015
02-28-2012, 03:08 PM
on which line does it crash?


It dosent return a line , the whole thing just crashes

But it seems like its on the second find it command after I change sheets


I was hoping there was a way to loop the first set of code with a new range
On the summary page

p45cal
02-28-2012, 03:55 PM
the whole thing just crashes You have to reboot the computer? Reboot Excel? What? Is there any error message - what does it say?

But it seems like its on the second find it command after I change sheets What makes you say it seems its on the second find it?

I was hoping there was a way to loop the first set of code with a new range On the summary pageThere is but I need to understand what crashes it first.
Can you attach the file here?

c7015
02-28-2012, 04:02 PM
this line

Set findit = Range("A28:A127").Find(what:=t20ra.Value)
If findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End If

as it clears the first range and then crashes without doing this line


it crashes the macro , not excel or the computer

p45cal
02-28-2012, 04:31 PM
So no error message at all.

Where is the code? A standard code module, a sheet code module, or even perhaps the Thisworkbook code module?

One thing I would do is to remove all (or as much as possible) Select operations. So the likes of:

findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

becomes:
With findit
Range(.Offset(1, 3), .Offset(1, 3).End(xlDown)).ClearContents
end with

I'll wait for a reply before suggesting any more.

c7015
02-28-2012, 05:02 PM
So no error message at all.

Where is the code? A standard code module, a sheet code module, or even perhaps the Thisworkbook code module?

One thing I would do is to remove all (or as much as possible) Select operations. So the likes of:

findit.Select
ActiveCell.Offset(1, 3).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

becomes:
With findit
Range(.Offset(1, 3), .Offset(1, 3).End(xlDown)).ClearContents
end with

I'll wait for a reply before suggesting any more.

It's just a standard code module , button clicks to export and trim all unessasary data ( ie anything past whatever age I type in my text box(s)(4 of them)

So say I type 85 in the text box called "rbox1" it will clear all data in colum c after it finds the rbox 1 value

There are 4 text boxes and as many as 3 at a time might have to be trimmed at different values

I will attach the sheet when I get back to my computer

c7015
02-29-2012, 07:49 AM
Ok P45 , here is the sheet

c7015
02-29-2012, 07:49 AM
here are some typical values

http://img341.imageshack.us/img341/94/diag.png[/URL]




Uploaded with ImageShack.us (http://imageshack.us)
and the button that I am working on

c7015
02-29-2012, 07:55 AM
http://img401.imageshack.us/img401/9131/diag2.png[/URL]

as you can see it works to clear what I want on the page "calculations"


but fails on the page Summary (however it does the first opperation clearing column c at the correct age ??

http://img210.imageshack.us/img210/6823/84470186.png

p45cal
02-29-2012, 08:47 AM
The code is not crashing at all. It is coming to and End statement, because t20ra was empty so nothing was found in the range searched.
End is a bit draconian:"Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement and to clear variables."

What do you want to happen when something like this is encountered.

c7015
02-29-2012, 08:55 AM
The code is not crashing at all. It is coming to and End statement, because t20ra was empty so nothing was found in the range searched.
End is a bit draconian:"Terminates execution immediately. Never required by itself but may be placed anywhere in a procedure to end code execution, close files opened with the Open statement and to clear variables."

What do you want to happen when something like this is encountered.


well If there is no value in t20 ra it means it is not being compaired and can just move onto the next statement

how come it does not end the program when it is checkcking the first page calculations ..


I would like it to do exactly what it does on the first page but on the sheet summary , is it just where I have place the end statement

c7015
02-29-2012, 09:22 AM
ok so I just took out the end and it works , but not sure why its different from the fist set to the second ...

p45cal
02-29-2012, 09:36 AM
well If there is no value in t20 ra it means it is not being compaired and can just move onto the next statement

how come it does not end the program when it is checkcking the first page calculations ..
Because on the first sheet you're searching ranges A21:A130, which includes some empty cells, but on the Summary sheet, your code only searched A28:A127, which doesn't include empty cells.

I would like it to do exactly what it does on the first page but on the sheet summary , is it just where I have place the end statementNo.

You need to change the likes ofIf findit Is Nothing Then
End
Else
findit.Select
ActiveCell.Offset(1, 5).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
End Ifto:If not findit Is Nothing Then Range(findit.Offset(1, 5), findit.Offset(1, 5).End(xlDown)).ClearContentsYes, one line.