Code copying wrong formula into cells

gheyman

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,347
Office Version
  1. 365
Platform
  1. Windows
Wehn I run this Code the fomula from Range("E11").Select ends up in all three rows.
Not sure why. I am telling it to select the cell, therfore thats the activeCell.

Any help appreciated!!


Range("E9").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D10"")),"""",INDIRECT(R6C&""!D10""))"
Range("E10").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D11"")),"""",INDIRECT(R6C&""!D11""))"
Range("E11").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D12"")),"""",INDIRECT(R6C&""!D12""))"
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
This is what I got when I ran your code...
Cell Formulas
RangeFormula
E9=IF(ISERROR(INDIRECT(E$6&"!D10")),"",INDIRECT(E$6&"!D10"))
E10=IF(ISERROR(INDIRECT(E$6&"!D11")),"",INDIRECT(E$6&"!D11"))
E11=IF(ISERROR(INDIRECT(E$6&"!D12")),"",INDIRECT(E$6&"!D12"))

I'm not sure what you're going for if this isn't it.

On another note, you don't have to select the cells. The code below would do the same as your original code...
Code: 
    Range("E9").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!D10"")),"""",INDIRECT(R6C&""!D10""))"
    Range("E10").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!D11"")),"""",INDIRECT(R6C&""!D11""))"
    Range("E11").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!D12"")),"""",INDIRECT(R6C&""!D12""))"
 
Upvote 0
I am surprised your results are different then mine. I am getting

=IF(ISERROR(INDIRECT(E$6&"!D12")),"",INDIRECT(E$6&"!D12"))

in cells E9, E10 and E11 they all end up with this formula in it.
 
Upvote 0
I took out the select as recomended and I am still getting the same results.

Here is the entire code. I run this inependently, so I know nothing else is causing the problem

Sub SetFormulas()
'Place Formulas in columns D & e
'Column D

Range("D8").FormulaR1C1 = _
"=IF(ISERROR((SUM(RC[1]:RC[205]))),"""",SUM(RC[1]:RC[205]))"
Range("D8").Select
Selection.Copy
Range("D8:D54").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
'Column E
'Total Price in Row 7
Range("E7").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!F82"")),"""",INDIRECT(R6C&""!F82""))"
'Hours
Range("E9").Select
Range("E9").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D10"")),"""",INDIRECT(R6C&""!D10""))"
Range("E10").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D11"")),"""",INDIRECT(R6C&""!D11""))"
Range("E11").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D12"")),"""",INDIRECT(R6C&""!D12""))"
Range("E12").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D13"")),"""",INDIRECT(R6C&""!D13""))"
Range("E13").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D14"")),"""",INDIRECT(R6C&""!D14""))"
Range("E14").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D15"")),"""",INDIRECT(R6C&""!D15""))"
Range("E15").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D16"")),"""",INDIRECT(R6C&""!D16""))"
Range("E16").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D17"")),"""",INDIRECT(R6C&""!D17""))"
Range("E17").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D18"")),"""",INDIRECT(R6C&""!D18""))"
Range("E18").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D19"")),"""",INDIRECT(R6C&""!D19""))"
Range("E20").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D21"")),"""",INDIRECT(R6C&""!D21""))"
Range("E21").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D22"")),"""",INDIRECT(R6C&""!D22""))"
Range("E22").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D23"")),"""",INDIRECT(R6C&""!D23""))"
Range("E23").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D24"")),"""",INDIRECT(R6C&""!D24""))"
Range("E24").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D25"")),"""",INDIRECT(R6C&""!D25""))"
Range("E25").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D26"")),"""",INDIRECT(R6C&""!D26""))"
Range("E26").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D27"")),"""",INDIRECT(R6C&""!D27""))"
Range("E27").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D28"")),"""",INDIRECT(R6C&""!D28""))"
Range("E29").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D30"")),"""",INDIRECT(R6C&""!D30""))"
Range("E30").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D31"")),"""",INDIRECT(R6C&""!D31""))"
Range("E31").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D32"")),"""",INDIRECT(R6C&""!D32""))"
Range("E32").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D33"")),"""",INDIRECT(R6C&""!D33""))"
Range("E33").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D34"")),"""",INDIRECT(R6C&""!D34""))"
Range("E34").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D35"")),"""",INDIRECT(R6C&""!D35""))"
Range("E35").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D36"")),"""",INDIRECT(R6C&""!D36""))"
Range("E37").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D38"")),"""",INDIRECT(R6C&""!D38""))"
Range("E38").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D39"")),"""",INDIRECT(R6C&""!D39""))"
Range("E39").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D40"")),"""",INDIRECT(R6C&""!D40""))"
Range("E40").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D41"")),"""",INDIRECT(R6C&""!D41""))"
Range("E42").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D43"")),"""",INDIRECT(R6C&""!D43""))"
Range("E43").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D44"")),"""",INDIRECT(R6C&""!D44""))"
Range("E44").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D45"")),"""",INDIRECT(R6C&""!D45""))"
Range("E45").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D46"")),"""",INDIRECT(R6C&""!D46""))"
Range("E46").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D48"")),"""",INDIRECT(R6C&""!D48""))"
'Dollars & ODC
Range("E48").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D56"")),"""",INDIRECT(R6C&""!D56""))"
Range("E49").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D57"")),"""",INDIRECT(R6C&""!D57""))"
Range("E50").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D60"")),"""",INDIRECT(R6C&""!D60""))"
Range("E51").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!D61"")),"""",INDIRECT(R6C&""!651""))"
Range("E52").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!F62"")),"""",INDIRECT(R6C&""!F62""))"
Range("E53").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!F65"")),"""",INDIRECT(R6C&""!F65""))"
Range("E54").FormulaR1C1 = _
"=IF(ISERROR(INDIRECT(R6C&""!F80"")),"""",INDIRECT(R6C&""!F80""))"
End Sub
 
Upvote 0
Your code works for me.

Each cell in column E has a different formula.

Here's your code shortened...
Code: 
Sub SetFormulas()
    'Place Formulas in columns D & e
    'Column D
    
    Range("D8:D54").FormulaR1C1 = _
    "=IF(ISERROR((SUM(RC[1]:RC[205]))),"""",SUM(RC[1]:RC[205]))"

    'Total Price in Row 7
    Range("E7").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!F82"")),"""",INDIRECT(R6C&""!F82""))"
    
    'Hours
    For i = 10 To 46
        Range("E9").Offset(i - 10).FormulaR1C1 = _
        "=IF(ISERROR(INDIRECT(R6C&""!D" & i & """)),"""",INDIRECT(R6C&""!D" & i & """))"
    Next i
    Range("E19, E28, E36, E41").ClearContents
    
    Range("E46").FormulaR1C1 = _
        "=IF(ISERROR(INDIRECT(R6C&""!D48"")),"""",INDIRECT(R6C&""!D48""))"

    'Dollars & ODC
    Range("E48").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!D56"")),"""",INDIRECT(R6C&""!D56""))"
    Range("E49").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!D57"")),"""",INDIRECT(R6C&""!D57""))"
    Range("E50").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!D60"")),"""",INDIRECT(R6C&""!D60""))"
    Range("E51").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!D61"")),"""",INDIRECT(R6C&""!651""))"
    Range("E52").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!F62"")),"""",INDIRECT(R6C&""!F62""))"
    Range("E53").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!F65"")),"""",INDIRECT(R6C&""!F65""))"
    Range("E54").FormulaR1C1 = _
    "=IF(ISERROR(INDIRECT(R6C&""!F80"")),"""",INDIRECT(R6C&""!F80""))"
    
End Sub
 
Upvote 0
Other than shortening the code, you also solved the problem

Once again, very much appreciate Alpha frogs knowledge!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
238
SBExcel123
S
V
  • Solved
Copy only Value not Formula to new Cell location after ActiveX Macro button pushed.
Replies
2
Views
165
VinnyDoesntKnowExcelCode
V
K
  • Question Question
Populate With Criteria
Replies
3
Views
83
Peter_SSs
Peter_SSs
gtd526
  • Solved
Get the Row number for Matched cell value
Replies
2
Views
410
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
368
gtd526
gtd526

Forum statistics

Threads
1,224,590
Messages
6,179,762
Members
452,940
Latest member
rootytrip

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top