Help - Selecting and pasting into range

  • Thread starter Thread starter amonymous
  • Start date Start date
A

amonymous

Based on this process:

Alternative procedure to copy a formula down for the range
D22:D125:

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

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.

Is this the best way to go about this? and
Is this the most optimal code to write?

Anyone?

Thanx.
 
I assume the formula in D22 is

=D21+5 (where 5 represents your increment value.

To fill the range with the formula do this

Range("D22:D125").Formula = "=D21+5"

since you are using relative addresses in the formula, the formula will be
adjusted.

If you want to generalize
Sub AddFormula()
Dim Start_Cell As String
Dim Beg_Val As Double
Dim End_Val As Double
Dim incr As Double
Start_Cell = "E5"
Beg_Val = 21
End_Val = 101
incr = 5

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

End Sub

Excel will coerce incr to a string, so you don't need to do an explicit
conversion, but you can if you want to.

--
Regards,
Tom Ogilvy

amonymous said:
Based on this process:

Alternative procedure to copy a formula down for the range
D22:D125:

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

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.

Is this the best way to go about this? and
Is this the most optimal code to write?

Anyone?

Thanx.
Click to expand...
 
Thanx Tom.

I'm not sure if this sub was inteneded to stand alone or not.

I assumed it was and so adjusted my example to incorporate your
code 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 = 21
' End_Val = 101
' Incr = 5

Application.ScreenUpdating = False

Dim Start_Cell As String
Dim Beg_Val As Double
Dim End_Val As Double
Dim incr As Double

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
Unload DataFill

Application.ScreenUpdating = True

End Sub

Since I'm pulling my data from a userform, I didn't want to
state their values explicitly in the macro.

Every time I run this, it bombs at the numrows line and I get an
"overflow" error.

When I run the code as a standalone macro, it works fine. But, running
it as a standalone macro defeats the purpose of the userform.

Help?

I assume the formula in D22 is

=D21+5 (where 5 represents your increment value.

To fill the range with the formula do this

Range("D22:D125").Formula = "=D21+5"

since you are using relative addresses in the formula, the formula will be
adjusted.

If you want to generalize
Sub AddFormula()
Dim Start_Cell As String
Dim Beg_Val As Double
Dim End_Val As Double
Dim incr As Double
Start_Cell = "E5"
Beg_Val = 21
End_Val = 101
incr = 5

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

End Sub

Excel will coerce incr to a string, so you don't need to do an explicit
conversion, but you can if you want to.

--
Regards,
Tom Ogilvy

amonymous said:
Based on this process:

Alternative procedure to copy a formula down for the range
D22:D125:

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

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.

Is this the best way to go about this? and
Is this the most optimal code to write?

Anyone?

Thanx.
Click to expand...
Click to expand...
 
Got It!

Here's the code:

This is a simple userform with four textboxes:

Start_Cell
Beg_Val
End_Val
Incr

Used as described in the starting thread:

1/19 - "Help - Selecting and pasting into range"

quote:

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

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)
 
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.

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

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

Unload DataFill

Application.ScreenUpdating = True

End Sub
 
Back
Top