Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating column based on formula
I'm getting an "Application-defined or object-defined error" on the
line below that I've identified...can anyone help me out on this? Dim last7 As Long last7 = Worksheets("Working Sheet").Cells(Rows.Count, "J").End(xlUp).Row Range("K1").Formula = "=IF(SUM('Working Sheet'!RC[1]/MACRO!G $11$)),"",SUM('Working Sheet'!RC[1]/MACRO!G$11$)" <---LINE WITH ERROR Range("K1").AutoFill Destination:=Range("K1:K" & last7) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating column based on formula
The $ signs are on the wrong side....
Range("K1").Formula = "=IF(SUM('Working Sheet'!RC[1]/MACRO!$G$11)),"",SUM('Working Sheet'!RC[1]/MACRO!$G$11)" Rick "S Himmelrich" wrote in message ... I'm getting an "Application-defined or object-defined error" on the line below that I've identified...can anyone help me out on this? Dim last7 As Long last7 = Worksheets("Working Sheet").Cells(Rows.Count, "J").End(xlUp).Row Range("K1").Formula = "=IF(SUM('Working Sheet'!RC[1]/MACRO!G $11$)),"",SUM('Working Sheet'!RC[1]/MACRO!G$11$)" <---LINE WITH ERROR Range("K1").AutoFill Destination:=Range("K1:K" & last7) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating column based on formula
You're mixing R1C1 reference style with A1 reference style.
If you decide to use all R1C1 reference style, then use .formulaR1C1 = (and just a little help: $g$11 is r11c7) S Himmelrich wrote: I'm getting an "Application-defined or object-defined error" on the line below that I've identified...can anyone help me out on this? Dim last7 As Long last7 = Worksheets("Working Sheet").Cells(Rows.Count, "J").End(xlUp).Row Range("K1").Formula = "=IF(SUM('Working Sheet'!RC[1]/MACRO!G $11$)),"",SUM('Working Sheet'!RC[1]/MACRO!G$11$)" <---LINE WITH ERROR Range("K1").AutoFill Destination:=Range("K1:K" & last7) -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating column based on formula
Ps. You have a couple of other things wrong with your formula.
If you need to have double quotes show up in your formula, then you'll need to double them in your code. And you don't need =sum(). You're just dividing one number by another. And it looks like you're putting this all on Working Sheet, if that's true, then you could try this: Dim last7 As Long With Worksheets("working sheet") last7 = .Cells(.Rows.Count, "J").End(xlUp).Row .Range("K1:K" & last7).FormulaR1C1 _ = "=IF(RC[1]/MACRO!r11c7,"""",RC[1]/MACRO!r11c7)" End With I'm not sure what you're doing with that formula, but maybe you were checking for errors??? = "=IF(iserror(RC[1]/MACRO!r11c7),"""",RC[1]/MACRO!r11c7)" is how I'd check. S Himmelrich wrote: I'm getting an "Application-defined or object-defined error" on the line below that I've identified...can anyone help me out on this? Dim last7 As Long last7 = Worksheets("Working Sheet").Cells(Rows.Count, "J").End(xlUp).Row Range("K1").Formula = "=IF(SUM('Working Sheet'!RC[1]/MACRO!G $11$)),"",SUM('Working Sheet'!RC[1]/MACRO!G$11$)" <---LINE WITH ERROR Range("K1").AutoFill Destination:=Range("K1:K" & last7) -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating column based on formula
Solution:
Dim rng As Range Set rng = Range(Cells(1, "D"), Cells(Rows.Count, "D").End(xlUp)) rng.Offset(0, 7).Formula = "=IF(J1="""","""",SUM(J1/MACRO!$G$11))" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating column based on formula
What happens if Macro!G11 is empty or is equal to 0?
Why use the =sum() function. Why did the column change from J to D? S Himmelrich wrote: Solution: Dim rng As Range Set rng = Range(Cells(1, "D"), Cells(Rows.Count, "D").End(xlUp)) rng.Offset(0, 7).Formula = "=IF(J1="""","""",SUM(J1/MACRO!$G$11))" -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating percentages based on the number of checked boxes in a column | Excel Programming | |||
Calculating sum based on value in adjacent column | Excel Discussion (Misc queries) | |||
Calculating totals in a column based on a lookup in another column | Excel Worksheet Functions | |||
Calculating a field based on what is in another column | Excel Discussion (Misc queries) | |||
Calculating values to column D with formula based on values column A | Excel Programming |