Runtime error 1004 when trying to insert formula in cells

J

jan120253

I have a sheet with some numbers in some cells, while other celles are empty. Now I try to replace the numbers with a certain formula, but it returns a runtime error 1004, Application-defines og obeject-defined error.

The macro looks like this:

Sub FyldUdfyldte()

Dim Indhold As String
Indhold = InputBox("Type needed formula"")

For Each c In Selection.Cells
If Not IsEmpty(c) Then c.Value = Indhold
Next c

End Sub

The formula looks like this

=IF(VLOOKUP($A31,Opslag!$A:$M,F$1,FALSE)>0,VLOOKUP($A31,Opslag!$A:$M,F$1,FALSE),"")


"Opslag" is another sheet, from where to get new numbers.

Any ideas?

Jan
 
C

Claus Busch

Hi Jan,

Am Sun, 5 Oct 2014 04:24:24 -0700 (PDT) schrieb (e-mail address removed):
I have a sheet with some numbers in some cells, while other celles are empty. Now I try to replace the numbers with a certain formula, but it returns a runtime error 1004, Application-defines og obeject-defined error.
Click to expand...
=IF(VLOOKUP($A31,Opslag!$A:$M,F$1,FALSE)>0,VLOOKUP($A31,Opslag!$A:$M,F$1,FALSE),"")
Click to expand...

try:

Sub FyldUdfyldte()
Dim c As Range
Dim Indhold As String

Indhold = InputBox("Type needed formula")

For Each c In Selection
If Not IsEmpty(c) Then c.Formula = Indhold
Next c
End Sub


Regards
Claus B.
 
J

jan120253

Indhold = InputBox("Type needed formula")



For Each c In Selection

If Not IsEmpty(c) Then c.Formula = Indhold

Next c

End Sub
Click to expand...

Claus - I have aldready tried that, but get the same runtime error.

Jan
 
C

Claus Busch

Hi Jan,

Am Sun, 5 Oct 2014 04:57:40 -0700 (PDT) schrieb (e-mail address removed):
Claus - I have aldready tried that, but get the same runtime error.
Click to expand...

I tested it here without any error.
Did you select some cells before running the macro?
Do you write a "=" in front of the formula into the InputBox?
Have you tried it with Formula.Local that you can use your function
names and your separators?


Regards
Claus B.
 
J

jan120253

Den søndag den 5. oktober 2014 14.11.07 UTC+2 skrev Claus Busch:

Thank You. FormulaLocal did the trick. I should have thought of that.

Jan
 
J

jan120253

Hi Claus

You solution of using FormulaLocal Work nicely when I treid to insert this formula in the inputbox

=HVIS(LOPSLAG($A31;Opslag!$A:$M;F$1;FALSK)>0;LOPSLAG($A31;Opslag!$A:$M;F$1;FALSK);"")

but it fails with an "Application defined or objectdefined error" in this line

If Not IsEmpty(c) Then c.FormulaLocal = Indhold

when I try with the formula below

=HVIS(HVIS(ER.FEJL(LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK));LOPSLAG($B1147;'2015priser'!$B:$HG;DV$1;FALSK);LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK))=0;AFRUND(LOPSLAG($B1147;'2015priser'!$B:$HG;DV$2;FALSK);0);AFRUND(HVIS(ER.FEJL(LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK));LOPSLAG($B1147;'2015priser'!$B:$HG;DV$1;FALSK);LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK));0))

is there a lenght restriction on FormulaLocal or what elese can be wrong?

Jan
 
C

Claus Busch

Hi Jan,

Am Sun, 26 Oct 2014 04:12:17 -0700 (PDT) schrieb (e-mail address removed):
=HVIS(HVIS(ER.FEJL(LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK));LOPSLAG($B1147;'2015priser'!$B:$HG;DV$1;FALSK);LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK))=0;AFRUND(LOPSLAG($B1147;'2015priser'!$B:$HG;DV$2;FALSK);0);AFRUND(HVIS(ER.FEJL(LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK));LOPSLAG($B1147;'2015priser'!$B:$HG;DV$1;FALSK);LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK));0))
Click to expand...

can you offer this formula in English?
I guess there is a typo anywhere in this formula


Regards
Claus B.
 
C

Claus Busch

Hi,

Am Sun, 26 Oct 2014 12:21:43 +0100 schrieb Claus Busch:
can you offer this formula in English?
I guess there is a typo anywhere in this formula
Click to expand...

or the sheet name differs in spelling. Check for trailing or leading
spaces in the sheet names


Regards
Claus B.
 
J

jan120253

Imn English the formula is

=IF(IF(ISERROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015priser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE))=0,ROUND(VLOOKUP($B1147,'2015priser'!$B:$HG,DV$2,FALSE),0),ROUND(IF(ISERROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015priser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),0))

I have checked for Spaces, but I may have overlooked some. The names are spelled correct.

Jan


Den søndag den 26. oktober 2014 12.23.05 UTC+1 skrev Claus Busch:
 
C

Claus Busch

Hi Jan,

Am Sun, 26 Oct 2014 04:45:24 -0700 (PDT) schrieb (e-mail address removed):
=IF(IF(ISERROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015priser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE))=0,ROUND(VLOOKUP($B1147,'2015priser'!$B:$HG,DV$2,FALSE),0),ROUND(IF(ISERROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015priser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),0))
Click to expand...

your formula is wrong. Tell us in words what you want to calculate


Regards
Claus B.
 
J

jan120253

Sorry to have kept you waiting, suddenly had to Work :-(

The formula Works, when I type it in a cell, but wont be inserted via VBA.

I have to sheets, with app. 140.000 cells, from A1 to DU1140. Both sheets has Unique numbers in column A and headers in row 1. The first sheet contains actual prizes, for different items. There are only prizes in some cells,and what cells are empty and filled are not patterned in any way, its almost random.

The otherf hseet is updated with new prizes, which can be in same or other cells as in sheet 1. Now I want to insert a lookup formula in the cells, and only the cells in sheet 1, that has already numbers in them. This formulashould look up prizes in sheet 2, if such exist and return them to sheet 1, if the corresponding cell in sheet one is not empty. If it is, should notbe inserted.

As I mentioned the formula works if I type it, but I can'øt see why the VBA code suddenly fails, when it works with other, shorter formulas.

Jan

Den søndag den 26. oktober 2014 13.11.38 UTC+1 skrev Claus Busch:
 
C

Claus Busch

Hi Jan,

Am Mon, 27 Oct 2014 15:28:04 -0700 (PDT) schrieb (e-mail address removed):
I have to sheets, with app. 140.000 cells, from A1 to DU1140. Both sheets has Unique numbers in column A and headers in row 1. The first sheet contains actual prizes, for different items. There are only prizes in some cells, and what cells are empty and filled are not patterned in any way, its almost random.

The otherf hseet is updated with new prizes, which can be in same or other cells as in sheet 1. Now I want to insert a lookup formula in the cells, and only the cells in sheet 1, that has already numbers in them. This formula should look up prizes in sheet 2, if such exist and return them to sheet 1, if the corresponding cell in sheet one is not empty. If it is, should not be inserted.
Click to expand...

a IF formula needs 3 arguments and the structure is
=IF(Condition; True Part; False Part)

A nested IF formula looks like
IF(Condition1;True Part; IF(Condition2;True Part;IF(Condition3;True Part;False Part)))

To get the correct result the conditions must be listed from highest
priority to the lowest.

What is your order of priority? And what Excel version do you use?


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Runtime Error 1004 when runing Sub() from btnClick 2
debug runtime error 1004 2
VBA Runtime error '1004' 6
Application.undo runtime error '1004' 1
runtime 1004 error on deleting rows with blank cells 5
Getting error 1004 trying to write formula in cell 6
Error 1004 3
Runtime Error 1004??? 5

Top