Solved: Range Copy problem

I've constructed a formula. I put this formula in a cell and then attempt to copy it to a large number of cells via various Range copy techniques. I can get none to work. All are complaining about various object errors. The attempts at copy were commented out AFTER I tried and failed with them.

[vba] Cells(lastRow + 7, 2) = formRank
'
' Copy this formula
'
'Worksheets(rankSheet.Name).Range(Cells(lastRow + 7, 2)).Select

'Selection.Copy

'Worksheets(rankSheet.Name).Range(Cells(lastRow + 7, 2), _
Cells(lastRow + lastRow + 7, col)).Select

'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

'ActiveSheet.Range(Cells(lastRow + 7, 2)).Select

'Selection.Copy

'ActiveSheet.Range(Cells(lastRow + 7, 2), _
' Cells(lastRow + lastRow + 7, col)).Select

'ActiveSheet.Range(Cells(lastRow + 7, 2)).Formula = _
' ActiveSheet.Range(Cells(lastRow + 7, 2), _
' Cells(lastRow + lastRow + 7, col)).Formula

'Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

'rankSheet.Range(Cells(lastRow + 7, 2)).Copy _
' Destination:=rankSheet.Range(Cells(lastRow + 7, 2), Cells(lastRow + lastRow + 7, col))

'ActiveWorkbook.Worksheets(rankSheet.Name). _
' Range(Cells(lastRow + 7, 2)).Copy _
' Destination:=rankSheet.Range(Cells(lastRow + 7, 2), _
' Cells(lastRow + lastRow + 7, col))
[/vba]
I've done something very similar in another macro, but I just can't get this one to work.

Any ideas?

Code:

---

myBigRange.FormulaR1C1 = myOneCell.FormulaR1C1

---

[vba]

With Worksheets(rankSheet.Name)

With .Range(.Cells(LastRow + 7, 2))

.AutoFill.Resize (LastRow + LastRow + 7)
End With
End With
[/vba]

Thanks to both contributors.


Unfortunately, I get error 1004 (Application-defined or object-defined error) using both code sets and I have no idea what I've done wrong. I've seen this error in some of my previous attempts. My guess is there is something wrong with "rankSheet", but I set it with

[vba] Set rankSheet = ActiveWindow.ActiveSheet [/vba]

rankSheet was created thusly...

[vba] ActiveWorkbook.Sheets(fromSheet.Name).Copy _
After:=ActiveWorkbook.Sheets(1)

ActiveWindow.ActiveSheet.Name = rnkName [/vba]

Here is all the relevant code:

[vba]
ActiveWorkbook.Sheets(fromSheet.Name).Copy _
After:=ActiveWorkbook.Sheets(1)

ActiveWindow.ActiveSheet.Name = rnkName
'
' Since we copied all the data, get out of the input sheet
'
Set fromSheet = Nothing
Set rankSheet = ActiveWindow.ActiveSheet

'
' Now build rank data on rank sheet below normal data
'
Cells(lastRow + 7, 2) = formRank
'
' Copy this formula
'
Range(Cells(lastRow + lastRow + 7, col)).FormulaR1C1 = Range(Cells(lastRow + 7, 2)).FormulaR1C1
'With Worksheets(rankSheet.Name)

' With .Range(.Cells(lastRow + 7, 2))

' .AutoFill.Resize (lastRow + lastRow + 7)
' End With
'End With [/vba]

I apologize for the redundancy in the code listing. I think it's easier to illustrate with all the relevant code together. Both attempts fail with the same error 1004.

The problem isn't with rankSheet is probably to do with the unqualified use of Cells.

And xld has shown you how to resolve that using a little thing called the dot qualifier . and With.

Thanks for your assistance.




I modified Mike Rickson's proposal as I understood your suggestion:

[vba] Range(rankSheet.Cells(lastRow + lastRow + 7, col)).FormulaR1C1 = _
Range(rankSheet.Cells(lastRow + 7, 2)).FormulaR1C1 [/vba]

Unfortunately, I get the same result (1004 error).




I was unable to understand how to modify XLD's suggestion. My understanding of the "With" construct was that it was a "drill down", so the initial "With" unequivocally identifies the worksheet in question. Here is some pseudocode to illustrate:

[vba] With A
.b = 100
End With [/vba]

is equivalent to:

[vba]A.b = 100 [/vba]

Thanks for the suggestion

Try
[VBA]rankSheet.Cells(lastRow + lastRow + 7, col).FormulaR1C1 = rankSheet.Cells(lastRow + 7, 2).FormulaR1C1[/VBA]

I put your statement in verbatim. It no longer errors, I just don't get the results (spreadsheet looks same before and after).

The code does turn screenupdating off, but the fomula cell shows as expected.

I have a really hard time believing this, since I have very similar code working in another macro.

Thanks again for your assistance.

I did some more testing. I took out the "turn off recalculation" stuff. The code gives no error, but it doesn't propogate the formula either.

My intuition tells me there is not enough information in the suggested code UNLESS Excel expects that the named cells are overlapping. The "from" cell is the top left cell, and the "to" cell is the bottom right. Unless this construct knows this implicitly, then the statement does not have enough information.

What cell do you want the formula to come from?
What range of cells do you want filled with the formula?

Alternatly, if the formula is put in the starting cell with VB, the steps "put in cell, read from cell" can be skipped and one can go directly to "put in full range of cells".

Thanks to all responders.

Despite the several ways I tried to solve this problem, for some strange reason (mostly error 1004), I could not get it to work. Mike's suggestion ultimately led me to this solution:

[vba] Set uRng = Range(rankSheet.Cells(lastRow + 7, 2), _
rankSheet.Cells(lastRow + lastRow + 4, col))

uRng.Formula = formRank [/vba]

This worked.

I discovered that I could get around many errors (although I would ultimately get the 1004) by always using the :

[vba]Range(rankSheet.Cells(lastRow + 7,2), rankSheet.Cells(lastRow + 7, 2))[/vba]
instead of:
[vba]Range(rankSheet.Cells(lastRow + 7, 2))[/vba]

Both of these "seem" equivalent to me, but I got errors when I used the former instead of the latter.

Thanks again for all the help.

Just use

[vba]

rankSheet.Cells(lastRow + 7, 2)
[/vba]

You said:
[vba]ranksheet.Cells(lastRow + 7, 2)[/vba] I am confused because this only refers to a single cell, and the objective is to make 33 rows of cells contain this formula. I think there are 31 colums, so this would be 31x33 cells.

The thing that's confusing me again is that I have VERY similar code in another macro and it inserts a formula using a starting rangle with only one cell reference. The second reference uses two cell references, and it works.

I also got this to work as stated in my last post, but I have no clue whatsoever why my original attempt and many others failed. My original code placed the formula in a cell and then did a "paste special" to copy this cell to the entire range. I can see that this technique is probably much more efficient and more compactly stated.