byronwall / bUTL

Excel add-in with helpers for charting, formatting, and general pain points
http://byroni.us/bUTL
MIT License
16 stars 3 forks source link

Would this be useful? Prompt user to get every nth value from column #29

Closed RaymondWise closed 9 years ago

RaymondWise commented 9 years ago

Yeah the title wasn't long enough. A lot of times people ask "I want to get every nth cell from a column HOWTO?" I thought I'd write a sub to walk them through it - not sure if it'd be useful or not to this toolpak.

Sub GetEveryNth()

On Error GoTo endtimes
Dim begin As Range
Set begin = Application.InputBox("Where does your range begin?", Type:=8)

If begin.Cells.Count > 1 Then
    Set begin = begin.Cells(1)
End If

Dim x As Integer
x = Application.InputBox("You'd like to get cells at what interval?", Type:=1)

Dim nth As Integer
nth = Application.InputBox("Which occurence do you want to start on?", Type:=1)

Dim start As Range
Set start = Application.InputBox("Where would you like the output?", Type:=8)

If start.Cells.Count > 1 Then
    Set start = start.Cells(1)
End If

Dim formula As String
formula = "=OFFSET(" & begin.Address & ",Row(A" & nth & ")*" & x & ",0)"

Dim lastrow As Integer
lastrow = Cells(Rows.Count, begin.Column).End(xlUp)

Dim filltimes As Integer
filltimes = Int(lastrow / x) - nth + 1

Range(Cells(start.row, start.Column), Cells(filltimes, start.Column)).formula = formula
Exit Sub
endtimes:
MsgBox ("You did not provide enough valid input")
End Sub
byronwall commented 9 years ago

I've thought it would be good to have a tool that helps create formulas. I'm not aiming for help entering values into SUM like the Insert Function menu but instead something that helps build complicated formulas. I think there are some advanced formulas that could be entered more quickly and with fewer errors if the add-in is helping to build them. The idea would be something like a stored formula that allows you to forget about the boilerplate stuff. Those stored formulas would then be able to take inputs like "all unique values in this range" and then insert the probably complicated formula for that inside whatever formulas is trying to be built.

So I would definitely be interested in pursuing this general formula building tool. We will want to think about what formulas could be supported and make a list. After that, figuring out the ideal interface is probably next.

Does this sound like something that would be useful/worth building?

For the specific formula above, it looks good. I would use INDEX instead of OFFSET though. OFFSET is volatile and INDEX is not. INDEX just requires a larger range at the start, but that is usually doable. Worst case you use the entire sheet as the range and it reduces to how OFFSET works.

RaymondWise commented 9 years ago

Yeah, maybe it's a better bet to create a formula helping tool rather than to add it in to this one, it might get crowded.

byronwall commented 9 years ago

The formula helper could also be a part of bUTL. I didn't necessarily mean a separate tool. If nothing else, we can create something inside this tool and see where it goes. If it gets too big, it can be forked out into its own project/add-in.