PDA

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