Runtime error '1004' Application-defined or object-defined error regarding if formula

[j.smith1981]

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:

[VBA]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[/VBA]

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]

Ahh sorry, my mistake!

I added too many double quotes at the end, should look like:

[VBA]Range("D1").Resize(LastRowPriceMargin).Formula = "=IF($E1=""LAS"",""IS LASER"",""IS INK"")"[/VBA]

Thanks for anyone looking at this though,
Jeremy.

[j.smith1981]

Ahh I'm stuck again.

Keeps coming up with the same error when I amend the above same formula but to:

[VBA]Range("D1").Resize(LastRowPriceMargin).Formula = "=IF($E1=""LAS"",=$E1*margin+costlaser,IF(=$E1=""INK"",$E1*margin+cost,"""" ))"[/VBA]

Does anyone have any ideas as to why its bringing up the same identical error?

Bit confused now,
Jeremy.

[Bob Phillips]

[vba]
sFormula = "=IF($E1=""LAS""=$E1*margin+costlaser,IF($E1=""INK"",$E1*margin+cost,"""")) "
Range("D1").Resize(LastRowPriceMargin).Formula = sFormula
[/vba]

[j.smith1981]

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:

[VBA]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
[/VBA]

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]

Sorry, I was sloppy, I meant

[vba]

sFormula = "=IF($E1=""LAS"",$E1*margin+costlaser,IF($E1=""INK"",$E1*margin+cost,"""")) "
Range("D1").Resize(LastRowPriceMargin).Formula = sFormula
[/vba]

[j.smith1981]

Sorry, I was sloppy, I meant

[vba]

sFormula = "=IF($E1=""LAS"",$E1*margin+costlaser,IF($E1=""INK"",$E1*margin+cost,"""")) "
Range("D1").Resize(LastRowPriceMargin).Formula = sFormula
[/vba]

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, but will give this one a try

Thanks again,
Jeremy

[j.smith1981]

Sorry its coming up with the same error again.

Not sure why, just cannot work this one out at all.

Any suggestions?

[Bob Phillips]

Post the workbook for us to look at.

[Bob Phillips]

BTW, I assume the formula in the cell should be

=IF($E1="LAS",$E1*margin+costlaser,IF($E1="INK",$E1*margin+cost,""))

[j.smith1981]

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]

This works in my tests, no loops, proper declaration of LastRowPrice Margin

[vba]

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
[/vba]

[j.smith1981]

Wonderful, it works!

I just forgot to concatinate parts of it!

Thanks ever so much for your help,
Jeremy