Paste formula into selection

[D.S.]

I'm trying to copy the existing formulae in a selected range <
Range("A22").Copy > , then paste into the cells immediately below them for
a number of rows. (The row count varies, depending on my query results.)

Problem now, is that my sheet has grown to the point, that I get an error
saying the selection is too large to paste into. I have changed my code to
increment through the rows 1 row at a time, and populate the cells with my
formulae, but this has slowed me down a bit.

Any ideas?

Here's the code I started with for the copy and paste.

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A22").Copy
'Range("A3" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

 

[JE McGimpsey]

Try this:

With Range("A22")
.AutoFill Range(.Cells, .Cells(1).End(xlDown)).Resize(, 4), _
Type:=xlFillDefault
End With

 

[D.S.]

Thanks, but still get a run time error, < Selection too large >

D.S.

 

[JE McGimpsey]

There's no need to do a selection with my code - as a quick fix, perhaps
you can select a single cell prior to running it. I just tested it on a
sheet with values in A2:A65535 and it worked fine.

Try eliminating the Selections from your code and using the range
objects directly. It makes your code smaller, faster, and, IMO, easier
to maintain.

 

[D.S.]

Sorry, but I'm afraid you lost me. Can you give me a little more detail
please.

D.S.

 

[JE McGimpsey]

Selecting a range is almost never necessary. Unfortunately, it's how the
macro recorder does everything, so it tends to be the mode of choice for
beginning VBA programmers.

For instance, if I tried to record the process that you're doing, the
recorder would produce this (BTW - I didn't get any errors regarding the
paste range, so I'm not sure what's causing your problem):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/18/2004 by J.E.
'

'
Range("A22").Select
Selection.Copy
Range("A265536").Select
Selection.PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

this can be much more efficiently written by addressing the range
objects directly:

Public Sub Macro1()
Range("A22").Copy Destination:= _
Range("A365536")
End Sub

To look at your code:

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A22").Copy
'Range("A3" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

You could write it without the selects as

Dim lngLastRow As Long
lngLastRow = Range("A2").End(xlDown).Row
Range("A22").Copy
Range("A3" & lngLastRow).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

In any case, the code I gave you earlier using Autofill doesn't depend
on Selections, so if you're getting an error that the paste area is too
large, it's at another point in your code.

 

[D.S.]

Still getting run time error "Selection too large". I replaced my code with
yours, even changed the variable, now dimensioned as <long> rather than
<string>, though I didn't think that was a problem. The following is my
code exact, including the formulas I am trying to populate (formulas use
VLookup to another sheet in same workbook).

I can only get this method to work if I copy into a range that's limited to
about 1000 rows, with each copy. So, with my present row count of 2285,
it's taking 3 lines of code to accomplish this.


Sub RePopulateCells()
'This code will replinish the forumla in columns A

Dim lngLastRow As Long

lngLastRow = Range("A2").End(xlDown).Row
Range("A2" & lngLastRow).ClearContents 'CLEAR CELL CONTENTS OF _
COLUMNS A

lngLastRow = Range("G2").End(xlDown).Row 'DETERMINE LAST ROW _
TO WHICH FORMULA WILL BE FILLED

'WRITE FORMULA TO CELLS
Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO
KUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)<>"""",VLOOKUP(RC[7],JobNotes!C[1]:C[5]
,2,FALSE),""No""))) & "" "" & RC[32]"
Range("B2").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(RC[6],JobNotes!C[0]:C[4],3,FALSE)),"""",IF(VLOOKUP(RC[6
],JobNotes!C[0]:C[4],3,FALSE)<>"""",VLOOKUP(RC[6],JobNotes!C[0]:C[4],3,FALSE
),""""))"
Range("C2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)),"""",IF(VLOOK
UP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)=""Yes"",""Yes"","""")))"
Range("D2").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(RC[4],JobNotes!C[-2]:C[2],5,FALSE)),"""",IF(VLOOKUP(RC[
4],JobNotes!C[-2]:C[2],5,FALSE)<>"""",VLOOKUP(RC[4],JobNotes!C[-2]:C[2],5,FA
LSE),""""))"

'Range("A22").Copy Destination:=Range("A3", "D" & lngLastRow)
'THE ABOVE LINE WILL RETURN A RUN TIME ERROR


'THE FOLLOWING 3 LINES ARE THE ONLY WAY I CAN DO THIS _
WITHOUT GETTING A RUN TIME ERROR
Range("A22").Copy Destination:=Range("A31000")
Range("A10001000").Copy Destination:=Range("A10012000")
Range("A20002000").Copy Destination:=Range("A2001", "D" & lngLastRow)

Range("A2").Select 'BACK TO TOP OF SHEET

Calculate

End Sub


D.S.

 

[Dave Peterson]

You could turn calculation to manual, do your formula and then set it back the
way it was:

near the top:

Dim calcMode As Long
calcMode = Application.Calculation
Application.Calculation = xlCalculationManual

and right before the end sub:
Application.Calculation = calcMode

===
Another option is to apply the formulaR1C1 to each of the columns instead of
getting all 4 at once:

Range("A2:a" & lngLastRow).FormulaR1C1 = "your long formula here"

But you're doing a vlookup() 4 times and if it's not found, you're doing it 8
per row!

Maybe a helper column with a formula like:

Say you have that in column E(???):

range("e2").formular1c1 = "=MATCH(RC7,jobnotes!C2,0)"

If there's a match, you get the row into column B (C2 is column B in R1C1
notation).

=IF(ISERROR(E2),"No",INDEX(jobnotes!$C:$C,E2))

And you could wrap your if/then's around that.

Might make some difference in recalc time--but it'll mean more time to rewrite
your formulas.

Still getting run time error "Selection too large". I replaced my code with
yours, even changed the variable, now dimensioned as <long> rather than
<string>, though I didn't think that was a problem. The following is my
code exact, including the formulas I am trying to populate (formulas use
VLookup to another sheet in same workbook).

I can only get this method to work if I copy into a range that's limited to
about 1000 rows, with each copy. So, with my present row count of 2285,
it's taking 3 lines of code to accomplish this.

Sub RePopulateCells()
'This code will replinish the forumla in columns A

Dim lngLastRow As Long

lngLastRow = Range("A2").End(xlDown).Row
Range("A2" & lngLastRow).ClearContents 'CLEAR CELL CONTENTS OF _
COLUMNS A

lngLastRow = Range("G2").End(xlDown).Row 'DETERMINE LAST ROW _
TO WHICH FORMULA WILL BE FILLED

'WRITE FORMULA TO CELLS
Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO
KUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)<>"""",VLOOKUP(RC[7],JobNotes!C[1]:C[5]
,2,FALSE),""No""))) & "" "" & RC[32]"
Range("B2").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(RC[6],JobNotes!C[0]:C[4],3,FALSE)),"""",IF(VLOOKUP(RC[6
],JobNotes!C[0]:C[4],3,FALSE)<>"""",VLOOKUP(RC[6],JobNotes!C[0]:C[4],3,FALSE
),""""))"
Range("C2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)),"""",IF(VLOOK
UP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)=""Yes"",""Yes"","""")))"
Range("D2").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(RC[4],JobNotes!C[-2]:C[2],5,FALSE)),"""",IF(VLOOKUP(RC[
4],JobNotes!C[-2]:C[2],5,FALSE)<>"""",VLOOKUP(RC[4],JobNotes!C[-2]:C[2],5,FA
LSE),""""))"

'Range("A22").Copy Destination:=Range("A3", "D" & lngLastRow)
'THE ABOVE LINE WILL RETURN A RUN TIME ERROR

'THE FOLLOWING 3 LINES ARE THE ONLY WAY I CAN DO THIS _
WITHOUT GETTING A RUN TIME ERROR
Range("A22").Copy Destination:=Range("A31000")
Range("A10001000").Copy Destination:=Range("A10012000")
Range("A20002000").Copy Destination:=Range("A2001", "D" & lngLastRow)

Range("A2").Select 'BACK TO TOP OF SHEET

Calculate

End Sub

D.S.

Selecting a range is almost never necessary. Unfortunately, it's how the
macro recorder does everything, so it tends to be the mode of choice for
beginning VBA programmers.

For instance, if I tried to record the process that you're doing, the
recorder would produce this (BTW - I didn't get any errors regarding the
paste range, so I'm not sure what's causing your problem):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/18/2004 by J.E.
'

'
Range("A22").Select
Selection.Copy
Range("A265536").Select
Selection.PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

this can be much more efficiently written by addressing the range
objects directly:

Public Sub Macro1()
Range("A22").Copy Destination:= _
Range("A365536")
End Sub

To look at your code:

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A22").Copy
'Range("A3" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

You could write it without the selects as

Dim lngLastRow As Long
lngLastRow = Range("A2").End(xlDown).Row
Range("A22").Copy
Range("A3" & lngLastRow).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

In any case, the code I gave you earlier using Autofill doesn't depend
on Selections, so if you're getting an error that the paste area is too
large, it's at another point in your code.

 

[D.S.]

Had the autocalc covered, was going to do that with another procedure that
was going to call this procedure as well.

I did try one of your ideas though, copy each column individually rather
than A at once. That worked, no errors! Thanks for the idea.

D.S.

You could turn calculation to manual, do your formula and then set it back the
way it was:

near the top:

Dim calcMode As Long
calcMode = Application.Calculation
Application.Calculation = xlCalculationManual

and right before the end sub:
Application.Calculation = calcMode

===
Another option is to apply the formulaR1C1 to each of the columns instead of
getting all 4 at once:

Range("A2:a" & lngLastRow).FormulaR1C1 = "your long formula here"

But you're doing a vlookup() 4 times and if it's not found, you're doing it 8
per row!

Maybe a helper column with a formula like:

Say you have that in column E(???):

range("e2").formular1c1 = "=MATCH(RC7,jobnotes!C2,0)"

If there's a match, you get the row into column B (C2 is column B in R1C1
notation).

=IF(ISERROR(E2),"No",INDEX(jobnotes!$C:$C,E2))

And you could wrap your if/then's around that.

Might make some difference in recalc time--but it'll mean more time to rewrite
your formulas.

Still getting run time error "Selection too large". I replaced my code with
yours, even changed the variable, now dimensioned as <long> rather than
<string>, though I didn't think that was a problem. The following is my
code exact, including the formulas I am trying to populate (formulas use
VLookup to another sheet in same workbook).

I can only get this method to work if I copy into a range that's limited to
about 1000 rows, with each copy. So, with my present row count of 2285,
it's taking 3 lines of code to accomplish this.

Sub RePopulateCells()
'This code will replinish the forumla in columns A

Dim lngLastRow As Long

lngLastRow = Range("A2").End(xlDown).Row
Range("A2" & lngLastRow).ClearContents 'CLEAR CELL CONTENTS OF _
COLUMNS A

lngLastRow = Range("G2").End(xlDown).Row 'DETERMINE LAST ROW _
TO WHICH FORMULA WILL BE FILLED

'WRITE FORMULA TO CELLS
Range("A2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO

,2,FALSE),""No""))) & "" "" & RC[32]"
Range("B2").FormulaR1C1 =

"=IF(ISERROR(VLOOKUP(RC[6],JobNotes!C[0]:C[4],3,FALSE)),"""",IF(VLOOKUP(RC[6

],JobNotes!C[0]:C[4],3,FALSE)<>"""",VLOOKUP(RC[6],JobNotes!C[0]:C[4],3,FAL

SE

),""""))"
Range("C2").FormulaR1C1 =
"=UPPER(IF(ISERROR(VLOOKUP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)),"""",IF(VLOOK
UP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)=""Yes"",""Yes"","""")))"
Range("D2").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(RC[4],JobNotes!C[-2]:C[2],5,FALSE)),"""",IF(VLOOKUP(RC[

LSE),""""))"

'Range("A22").Copy Destination:=Range("A3", "D" & lngLastRow)
'THE ABOVE LINE WILL RETURN A RUN TIME ERROR

'THE FOLLOWING 3 LINES ARE THE ONLY WAY I CAN DO THIS _
WITHOUT GETTING A RUN TIME ERROR
Range("A22").Copy Destination:=Range("A31000")
Range("A10001000").Copy Destination:=Range("A10012000")
Range("A20002000").Copy Destination:=Range("A2001", "D" & lngLastRow)

Range("A2").Select 'BACK TO TOP OF SHEET

Calculate

End Sub

D.S.

Selecting a range is almost never necessary. Unfortunately, it's how the
macro recorder does everything, so it tends to be the mode of choice for
beginning VBA programmers.

For instance, if I tried to record the process that you're doing, the
recorder would produce this (BTW - I didn't get any errors regarding the
paste range, so I'm not sure what's causing your problem):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/18/2004 by J.E.
'

'
Range("A22").Select
Selection.Copy
Range("A265536").Select
Selection.PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

this can be much more efficiently written by addressing the range
objects directly:

Public Sub Macro1()
Range("A22").Copy Destination:= _
Range("A365536")
End Sub

To look at your code:

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A22").Copy
'Range("A3" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

You could write it without the selects as

Dim lngLastRow As Long
lngLastRow = Range("A2").End(xlDown).Row
Range("A22").Copy
Range("A3" & lngLastRow).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

In any case, the code I gave you earlier using Autofill doesn't depend
on Selections, so if you're getting an error that the paste area is too
large, it's at another point in your code.


Sorry, but I'm afraid you lost me. Can you give me a little more detail
please.

 

[D.S.]

Thanks to JE McGimpsey also, I incorporated your suggestion on the copy
method. That helped clean it up nicely.

Range("A2").Copy Range("A3", "A" & lngLastRow) 'smaller range, no errors!
Range("B2").Copy Range("B3", "B" & lngLastRow) 'smaller range, no errors!
Range("C2").Copy Range("C3", "C" & lngLastRow) 'smaller range, no errors!
Range("D2").Copy Range("D3", "D" & lngLastRow) 'smaller range, no errors!


D.S.

Had the autocalc covered, was going to do that with another procedure that
was going to call this procedure as well.

I did try one of your ideas though, copy each column individually rather
than A at once. That worked, no errors! Thanks for the idea.

D.S.

You could turn calculation to manual, do your formula and then set it

back

the
way it was:

near the top:

Dim calcMode As Long
calcMode = Application.Calculation
Application.Calculation = xlCalculationManual

and right before the end sub:
Application.Calculation = calcMode

===
Another option is to apply the formulaR1C1 to each of the columns

instead

of
getting all 4 at once:

Range("A2:a" & lngLastRow).FormulaR1C1 = "your long formula here"

But you're doing a vlookup() 4 times and if it's not found, you're doing it 8
per row!

Maybe a helper column with a formula like:

Say you have that in column E(???):

range("e2").formular1c1 = "=MATCH(RC7,jobnotes!C2,0)"

If there's a match, you get the row into column B (C2 is column B in R1C1
notation).

=IF(ISERROR(E2),"No",INDEX(jobnotes!$C:$C,E2))

And you could wrap your if/then's around that.

Might make some difference in recalc time--but it'll mean more time to rewrite
your formulas.

code

with

limited

to

"=UPPER(IF(ISERROR(VLOOKUP(RC[7],JobNotes!C[1]:C[5],2,FALSE)),""No"",IF(VLOO

,2,FALSE),""No""))) & "" "" & RC[32]"
Range("B2").FormulaR1C1 =

"=IF(ISERROR(VLOOKUP(RC[6],JobNotes!C[0]:C[4],3,FALSE)),"""",IF(VLOOKUP(RC[6

),""""))"
Range("C2").FormulaR1C1 =

"=UPPER(IF(ISERROR(VLOOKUP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)),"""",IF(VLOOK

UP(RC[5],JobNotes!C[-1]:C[3],4,FALSE)=""Yes"",""Yes"","""")))"
Range("D2").FormulaR1C1 =

"=IF(ISERROR(VLOOKUP(RC[4],JobNotes!C[-2]:C[2],5,FALSE)),"""",IF(VLOOKUP(RC[

LSE),""""))"

'Range("A22").Copy Destination:=Range("A3", "D" & lngLastRow)
'THE ABOVE LINE WILL RETURN A RUN TIME ERROR

'THE FOLLOWING 3 LINES ARE THE ONLY WAY I CAN DO THIS _
WITHOUT GETTING A RUN TIME ERROR
Range("A22").Copy Destination:=Range("A31000")
Range("A10001000").Copy Destination:=Range("A10012000")
Range("A20002000").Copy Destination:=Range("A2001", "D" & lngLastRow)

Range("A2").Select 'BACK TO TOP OF SHEET

Calculate

End Sub

D.S.

Selecting a range is almost never necessary. Unfortunately, it's how the
macro recorder does everything, so it tends to be the mode of choice for
beginning VBA programmers.

For instance, if I tried to record the process that you're doing, the
recorder would produce this (BTW - I didn't get any errors regarding the
paste range, so I'm not sure what's causing your problem):

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/18/2004 by J.E.
'

'
Range("A22").Select
Selection.Copy
Range("A265536").Select
Selection.PasteSpecial Paste:=xlFormulas, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

this can be much more efficiently written by addressing the range
objects directly:

Public Sub Macro1()
Range("A22").Copy Destination:= _
Range("A365536")
End Sub

To look at your code:

Dim strLastRow As String

Range("A2").End(xlDown).Select
strLastRow = ActiveCell.Row

'Range("A22").Copy
'Range("A3" & strLastRow).PasteSpecial xlPasteFormulas
'Application.CutCopyMode = False

You could write it without the selects as

Dim lngLastRow As Long
lngLastRow = Range("A2").End(xlDown).Row
Range("A22").Copy
Range("A3" & lngLastRow).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

In any case, the code I gave you earlier using Autofill doesn't depend
on Selections, so if you're getting an error that the paste area is too
large, it's at another point in your code.


Sorry, but I'm afraid you lost me. Can you give me a little more detail
please.