Help - Selecting and pasting into range

[spurtniq]

Here's my code, in a userform, based on this message:

'Filling down a column without dragging over every cell? '
(http://www.mrexcel.com/board2/viewtopic.php?t=68550&highlight=filling+column)

Relevant Section of msg:

Re: Filling down a column without dragging over every cell?

Alternative procedure to copy a formula down for the range
D2212500:

1 Select the cell with the formula (D22)
2 In the Name box, change D22 to D2212500
3 Press Enter to select
4 Press Ctrl-D (that's the Ctrl key and D together)

Regards,

Mike

Here's what I've got so far:

Private Sub OK_Button_Click()
Application.ScreenUpdating = False

Dim i As Integer

Set UserRange = Range(DataFill.Start_Cell)

Range(DataFill.Start_Cell).Value = CInt(Beg_Val)
End_Val = CInt(End_Val)
Incr = CInt(Incr)
Range(DataFill.Start_Cell).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=R[-1]C+" & Incr
Selection.Copy
i = (End_Val / Incr) - 1
ActiveCell.Offset(i, 0).Activate
Selection.FillDown

Unload DataFill

Application.ScreenUpdating = True

End Sub

I'm down to the point where I've selected and copied the formula.
Now I want to highlight the cells down, based on the fomula for
the variable "i" and then filldown the formula.

I'm trying, and not having much luck by trying to accomplish this
using some variant of "offset".

Basically, what I want to do is:

User input:

Start_Cell
Beg_Val
End_Val
Incr

Where what happens from there is:

Goto the Start_Cell
Enter the Beg_Val
Drop down one Cell
Enter the formula = (One Cell Up) + Incr Value
Copy this Cell
Select, Highlight, or Activate Cells down based on an offset of:

i = (End_Val / Incr) - 1

So, for:

Start_Cell = E5
Beg_Val = 3
End_Val = 30
Incr = 3

i = (30/3) - 1
i = (10) - 1
i = 9

(Based on the way I've got this setup so far, the end value could
be either -1 or -2 (i.e. (30/3) - 1 or (30/3) -2) depending on what
works best for this)

Would then drop down, highlight, select to E14
and copy or filldown the formula from E6 through to E14
creating the series:

3 - 30 in cells E5:e14 incremented by 3

So far, I'm not having much luck with this very last piece of
the puzzle.

Anyone?

Thanx.

 

[Jim Rech]

Well, this may be what you're looking for...

Sub a()
Dim StartCell As Range
Dim BegVal As Integer
Dim EndVal As Integer
Dim Incr As Integer

Set StartCell = Range("E5")
BegVal = 3
EndVal = 30
Incr = 3

StartCell.Value = BegVal
StartCell.Offset(1).Resize((EndVal - BegVal) / Incr).FormulaR1C1 = _
"=R[-1]C+" & Incr
End Sub

 

[spurtniq]

-
Originally posted by Jim Rech
-

WELL, THIS MAY BE WHAT YOU'RE LOOKING FOR...

SUB A()
DIM STARTCELL AS RANGE
DIM BEGVAL AS INTEGER
DIM ENDVAL AS INTEGER
DIM INCR AS INTEGER

SET STARTCELL = RANGE(\"E5\")
BEGVAL = 3
ENDVAL = 30
INCR = 3

STARTCELL.VALUE = BEGVAL
STARTCELL.OFFSET(1).RESIZE((ENDVAL - BEGVAL) / INCR).FORMULAR1C1 = _
\"=R[-1]C+\" & INCR
END SUB

Thanx, Jim.

As a standalone, it works fine.

However, I won't always be filling from the same start_cell.

When I plug it into my userform as follows:

Private Sub OK_Button_Click()
'
'UserForm named "DataFill" accepting variables
'
' Start_Cell
' Beg_Val
' End_Val
' Incr
'
' Example:
'
'Start_Cell = "E5"
'Beg_Val = 3
'End_Val = 30
'Incr = 3

Dim StartCell As Range
Dim BegVal As Integer
Dim EndVal As Integer
Dim Incr As Integer

Application.ScreenUpdating = False

StartCell.Value = BegVal
StartCell.Offset(1).Resize((EndVal - BegVal) / Incr).FormulaR1C1
_
"=R[-1]C+" & Incr

Application.ScreenUpdating = True

End Sub

I get the following error:

Object variable or With block variable not set (Error 91)

I have no idea what it means or how to fix it

 

[spurtniq]

Here's the code:

This is a simple userform with four textboxes:

Start_Cell
Beg_Val
End_Val
Incr

Used as described above.

Private Sub OK_Button_Click()
'
'UserForm named "DataFill" accepting variables
'
' Start_Cell
' Beg_Val
' End_Val
' Incr
'
' Example:
'
'Start_Cell = "E5"
'Beg_Val = 21
'End_Val = 101
'Incr = 5

Application.ScreenUpdating = False

Dim i As Integer

Set UserRange = Range(DataFill.Start_Cell)

Range(DataFill.Start_Cell).Value = CInt(Beg_Val)
End_Val = CInt(End_Val)
Incr = CInt(Incr)
Range(DataFill.Start_Cell).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=R[-1]C+" & Incr

numrows = Application.RoundUp((End_Val - Beg_Val) / Incr, 0)
Range(Start_Cell).Value = Beg_Val
Range(Start_Cell).Offset(1, 0).Resize(numrows).Formula = _
"=" & Range(Start_Cell).Address(0, 0) & "+" & Incr

ActiveSheet.Range("A1").Select

Unload DataFill

Application.ScreenUpdating = True

End Sub

BIG thanx to Tom Ogilvy for the missing piece (From "numrows" down to
Incr

 

[spurtniq]

My apologies to Tom.

The code solution for this problem is actually much simpler.

Much MORE Tom. Much less me.

Gonna get a handle on this code yet.

Here's the corrected code.

Credit given where it's due.