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

Hi Jan,

Am Sun, 5 Oct 2014 04:24:24 -0700 (PDT) schrieb :

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.

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

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Indhold = InputBox("Type needed formula")



For Each c In Selection

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

Next c

End Sub

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

Jan

Hi Jan,

Am Sun, 5 Oct 2014 04:57:40 -0700 (PDT) schrieb :

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

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

















Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

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;LOPSL AG($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;'2015p riser'!$B:$HG;DV$1;FALSK);LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK))=0;AFRUND(LOPSLAG($B11 47;'2015priser'!$B:$HG;DV$2;FALSK);0);AFRUND(HVIS( ER.FEJL(LOPSLAG($B1147;'JK-prisfil'!$C:$HG;DV$1;FALSK));LOPSLAG($B1147;'2015p riser'!$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