List vector using upper and lower limits

[Guest]

I am trying to reduce a column of data from ~8200 rows to ~1200 rows. I have
2 spin buttons that control the upper and lower limit the user desires. Is
there a way to reference just the cells within the range?

For instance . . . data is listed from A1 to A8200. A new list is generated
from the upper and lower limits depending on their respective spin button
value (ie. a lower limit value of 2481 would be the beginning point of the
'new' data range and an upper value of 3674 would be the end). The reduced
list (from A2481 to A3674) is then written in column 'B' and dyamically
changes with the spin buttons.

I appologize if this is too vague and thanks in advance.

 

[Dave Peterson]

Like in a worksheet formula?

I put a couple of spinbuttons on a worksheet and assigned the linked cells to B1
and C1.

Then I could use this:

=SUM(INDIRECT("A"&B1&":A"&C1))

To add the values in that truncated range.

 

[Guest]

Yes, preferrably a worksheet formula. But I do not wish to ADD the values of
the new data range; I only wish to COPY their values from their original
column (A1 - A8200) to a new column. In my previous example, the new/copied
range (dependent on the spin buttons current values) would be 1193 points
long (B1 - B1193). As the spin button values (C1 and C2 for instance)
change, the length of the new range (column B) should change dynamically.
Meaning if the lower limit value is changed to 2482 and the upper left alone,
column B will now have a data from B1 - B1192 (B1 = A2482 and B1192 = A3674
with all values in between.

 

[Dave Peterson]

I'm not sure how you're going to do it in a worksheet formula.

You could select B1:b8200 and then type this formula in B1 and hit
ctrl-shift-enter.

=INDIRECT("a"&c1&":A"&d1)

But that'll leave #n/a's for some of the cells.

I think I'd use code that would do the copying. I'd put a button next to the
spinners that did the work.

I put two spinners from the Forms toolbar on the worksheet. I put a button also
from the Forms toolbar right next to them.

I right clicked on each of the spinners and set the min and max to 1 and 8200.
I also used a linked cell, but that was only so I could see what was
happening--it wasn't required.

Then I rightclicked on the button and assigned it this macro:

Option Explicit
Sub testme()

Dim SPBtn1 As Spinner
Dim SPBtn2 As Spinner
Dim myRng As Range
Dim DestCell As Range

With ActiveSheet
Set SPBtn1 = .Spinners("spinner 1")
Set SPBtn2 = .Spinners("spinner 2")

Set myRng = .Range(.Cells(SPBtn1.Value, "A"), .Cells(SPBtn2.Value, "A"))

Set DestCell = .Range("b1")
DestCell.EntireColumn.ClearContents

DestCell.Resize(myRng.Rows.Count, myRng.Columns.Count).Value _
= myRng.Value
End With

End Sub

But 8200 is pretty far to go to use a spinner. I put two scrollbars (also from
the Forms toolbar in their place) and did the min/max stuff.

I used this for the macro for the button:

Option Explicit
Sub testme()

Dim SCBar1 As ScrollBar
Dim SCBar2 As ScrollBar
Dim myRng As Range
Dim DestCell As Range

With ActiveSheet
Set SCBar1 = .ScrollBars("scroll bar 1")
Set SCBar2 = .ScrollBars("scroll bar 2")

Set myRng = .Range(.Cells(SCBar1.Value, "A"), .Cells(SCBar2.Value, "A"))

Set DestCell = .Range("b1")
DestCell.EntireColumn.ClearContents

DestCell.Resize(myRng.Rows.Count, myRng.Columns.Count).Value _
= myRng.Value
End With

End Sub

You can see that it's pretty much the same code.

ps. After you add the controls (spinners or scrollbars), you can rightclick on
them and change their name in the NameBox (to the left of the formula bar).
Remember to hit enter when you're done typing the new name.

 

[Guest]

I can't quite get it to work. I have the code below entered and named the
spinners "spinner1" and "spinner2" under their properties menu. The command
button I left as default "CommandButton1. I receive an error at the line "Set
SPBtn1 = .Spinners("spinner1")" of Run-time error '1004': Unable to get the
Spinners property of the Worksheet class. When I step through the debugging
i get the same error 1004: Application-defined or object-defined error.
Thanks for the help.


Option Explicit
----------------------------------------------------------------
Sub testme()

Dim SPBtn1 As Spinner
Dim SPBtn2 As Spinner
Dim myRng As Range
Dim DestCell As Range

With ActiveSheet
Set SPBtn1 = .Spinners("spinner1")
Set SPBtn2 = .Spinners("spinner2")

Set myRng = .Range(.Cells(SPBtn1.Value, "B"), .Cells(SPBtn2.Value,
"B"))

Set DestCell = .Range("e2")
DestCell.EntireColumn.ClearContents

DestCell.Resize(myRng.Rows.Count, myRng.Columns.Count).Value _
= myRng.Value
End With

End Sub

-------------------------------------------------------------
Public Sub CommandButton1_click()
testme
End Sub

 

[Guest]

I worked around the problem by not referencing the spinbutton directly but by
referencing the linked cell. It works just the way I was intending. Thanks
for the help, Dave.

Philip

Dim myRng As Range
Dim DestCell As Range

With ActiveSheet

Set myRng = .Range(.Cells(SpinButton1.Value, "B"),
..Cells(SpinButton2.Value, "B"))

Set DestCell = .Range("e2")
DestCell.EntireColumn.ClearContents

DestCell.Resize(myRng.Rows.Count, myRng.Columns.Count).Value _
= myRng.Value
End With

 

[Dave Peterson]

Glad you got it working, but I'd bet it was the name of the spinner(s) that was
screwing things up. You had to use the name that you saw in the namebox (to the
left of the formula bar) when you selected that spinner(s).

(I find rightclicking on the object a quick way to select it.)