Would this be useful? Prompt user to get every nth value from column
Closed this issue · 3 comments
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
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.
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.
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.