-2

I have a pretty long macro and it is working fine at my work computer. The problem is that it is not working on my home computer. The macros are stored in personal xlsm file at roaming. So I just copy paste the personal file to my computer and run the macro. Everything is perfect until it needs to take a data from sheet2 to sheet1, in same excel workbook. It gives runtime 1004 object defined error. Any thoughts?

Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]<= " & MeanCov1 & ",-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[12]),IF(RC[-2]<= " & MeanCov2 & ",-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[" & WeekCov11 & "]),IF(RC[-2]<= " & MeanCov3 & ",-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[" & WeekCov22 & "]),-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[" & WeekCov33 & "]))))"
    Range("R2").Select
hakandeep
  • 51
  • 1
  • 10
  • 2
    Help us to help you, **post your current code.** – Gary's Student Aug 20 '16 at 15:13
  • 1
    Change `Thisworkbook` to `ActiveWorkbook` or even better, define and set a workbook-type variable and use a [With ... End With statement](https://msdn.microsoft.com/en-us/library/wc500chb.aspx). –  Aug 20 '16 at 15:17
  • it doesn't have a thisworkbook or activeworkbook code. It activates before that. I can't paste all the code since it is too long but i can send one of the problematic parts. Range("R2").Select ActiveCell.FormulaR1C1 = _ "=IF(RC[-2]<= " & MeanCov1 & ",-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[12]),IF(RC[-2]<= " & MeanCov2 & ",-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[" & WeekCov11 & "]),IF(RC[-2]<= " & MeanCov3 & ",-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[" & WeekCov22 & "]),-AVERAGE(Sheet2!R[3]C[-13]:R[3]C[" & WeekCov33 & "]))))" Range("R2").Select – hakandeep Aug 20 '16 at 15:26
  • 4
    Simplest answer: Stop using Select and Activate. See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. –  Aug 20 '16 at 15:33
  • but how to switch between two open workbooks without select? – hakandeep Aug 23 '16 at 06:02

1 Answers1

-1

Try using without select... Range("R2").FormulaR1C1 = ...

atclaus
  • 1,046
  • 1
  • 9
  • 12