View Full Version : Runtime error '1004' Application-defined or object-defined error regarding if formula
j.smith1981
06-14-2010, 08:32 AM
I am having problems using the below test macro, just to see if I can get the first stage of this if statement working for a formula.
Here's the macro part of it:
Dim margin As Double
Dim cost As Double
Cells(1, 4).Select
Selection.EntireColumn.Select
Selection.Insert Shift:=xlToLeft
Cells(1, 4).Select
Dim LastRowPriceMargin As String
LastRowPriceMargin = Cells(Rows.Count, "A").End(xlUp).Row
'Range("D1").Resize(LastRowPriceMargin).Formula = "=$E1" & "*" & margin & "+" & cost
Range("D1").Resize(LastRowPriceMargin).Formula = "=IF($E1=""LAS"",""IS LASER"",""IS INK"")"""
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
What it basically does is go to column D, inputs a value (or is supposed to), that tests if column E has value LAS (if this is true, D column brings up IS LASER) then likewise for the other value.
Can someone help me with this as to why it saying object defined error please?
I'd appreciate any advice,
Jeremy
j.smith1981
06-14-2010, 08:39 AM
Ahh sorry, my mistake!
I added too many double quotes at the end, should look like:
Range("D1").Resize(LastRowPriceMargin).Formula = "=IF($E1=""LAS"",""IS LASER"",""IS INK"")"
Thanks for anyone looking at this though,
Jeremy.
j.smith1981
06-14-2010, 09:06 AM
Ahh I'm stuck again.
Keeps coming up with the same error when I amend the above same formula but to:
Range("D1").Resize(LastRowPriceMargin).Formula = "=IF($E1=""LAS"",=$E1*margin+costlaser,IF(=$E1=""INK"",$E1*margin+cost,""""))"
Does anyone have any ideas as to why its bringing up the same identical error?
Bit confused now,
Jeremy.
Bob Phillips
06-14-2010, 12:36 PM
sFormula = "=IF($E1=""LAS""=$E1*margin+costlaser,IF($E1=""INK"",$E1*margin+cost,""""))"
Range("D1").Resize(LastRowPriceMargin).Formula = sFormula
j.smith1981
06-15-2010, 01:49 AM
Its still coming up with the same error.
I just dont get why, here's what I have so far, might shed some light on the problem:
Sub marginset()
Dim margin As Double
Dim cost As Double
Dim costlaser As Double
margin = 1.136
cost = 3.75
costlaser = 4
Cells(1, 4).Select
Selection.EntireColumn.Select
Selection.Insert Shift:=xlToLeft
Cells(1, 4).Select
Dim sFormula As String
Dim LastRowPriceMargin As String
sFormula = "=IF($E1=""LAS""=$E1*margin+costlaser,IF($E1=""INK"",$E1*margin+cost,""""))"
Range("D1").Resize(LastRowPriceMargin).Formula = sFormula
'Dim LastRowPriceMargin As String
'LastRowPriceMargin = Cells(Rows.Count, "A").End(xlUp).Row
'Range("D1").Resize(LastRowPriceMargin).Formula = "=$E1" & "*" & margin & "+" & cost
'Range("D1").Resize(LastRowPriceMargin).Formula = "=IF($E1=""LAS"",*margin+costlaser,""It ist not laser"")"
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Just trying to find random ideas to try and get it working as I am convinced this should work, any help again is greatly appreciated.
Thanks in advance again,
Jeremy
Bob Phillips
06-15-2010, 03:13 AM
Sorry, I was sloppy, I meant
sFormula = "=IF($E1=""LAS"",$E1*margin+costlaser,IF($E1=""INK"",$E1*margin+cost,""""))"
Range("D1").Resize(LastRowPriceMargin).Formula = sFormula
j.smith1981
06-15-2010, 03:19 AM
Sorry, I was sloppy, I meant
sFormula = "=IF($E1=""LAS"",$E1*margin+costlaser,IF($E1=""INK"",$E1*margin+cost,""""))"
Range("D1").Resize(LastRowPriceMargin).Formula = sFormula
Haha dont worry about it, Ive done the same many times, its just when I was fiddling with it, got to the point I couldnt remember what I had done.
Didnt have the best of days yesturday:banghead:, but will give this one a try:thumb
Thanks again,
Jeremy
j.smith1981
06-15-2010, 03:23 AM
Sorry its coming up with the same error again.
Not sure why, just cannot work this one out at all.
Any suggestions?
Bob Phillips
06-15-2010, 03:32 AM
Post the workbook for us to look at.
Bob Phillips
06-15-2010, 03:33 AM
BTW, I assume the formula in the cell should be
=IF($E1="LAS",$E1*margin+costlaser,IF($E1="INK",$E1*margin+cost,""))
j.smith1981
06-15-2010, 03:46 AM
I want the formula to appear in the end result (in the cell like this):
=IF(RC[1]="LAS",RC[-1]*1.136+4,IF(RC[1]="INK",RC[-1]*1.136+3.75,""))
1.136 is the 'margin' variable
3.75 is the 'cost' variable
and 4 is the 'costlaser' variable.
Then I want the above to appear in the cell when it goes through the macro, this works perfectly when inputting directly in the cell, how would be the best way to achieve this without a loop.
Any advice is greatly appreciated, sorry its just I cant seem to get this working, not as straight forward as I first thought.
Also keeping away from any loops, as there's roughy 1,888 rows this must go through, their only good for short amounts of data, would be easier but not exactly efficient.
Thanks for your help so far though,
Jeremy
Bob Phillips
06-15-2010, 04:19 AM
This works in my tests, no loops, proper declaration of LastRowPrice Margin
Dim LastRowPriceMargin As Long
LastRowPriceMargin = Cells(Rows.Count, "A").End(xlUp).Row
sFormula = "=IF(RC[1]=""LAS"",RC[1]*" & margin & "+" & costlaser & ",IF(RC[1]=""INK"",RC[1]*" & margin & "+" & cost & ",""""))"
Range("D1").Resize(LastRowPriceMargin).FormulaR1C1 = sFormula
j.smith1981
06-15-2010, 04:34 AM
Wonderful, it works!
I just forgot to concatinate parts of it!
Thanks ever so much for your help,
Jeremy
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.