Excel macro, stupid question 1

[ChristianDK]

Hello
I at trying to make a macro that adds a formula into an excel worksheet by combining text and values from the worksheet.

This works but will be inserted as text:

Range("JusteretEndelig!A1") = "VLOOKUP(A1;[kIM" & Range("JusteretEndelig!g1") & ".xls]JusteretEndelig!$A$1:$G$11;7;FALSE)"


The script i like to make is:

Range("JusteretEndelig!A1") = "=VLOOKUP(A1;[kIM" & Range("JusteretEndelig!g1") & ".xls]JusteretEndelig!$A$1:$G$11;7;FALSE)"

To point out the different in the to scripts it is the "=" in front of the formula.

Sadly it won't work, please help.

 

[combo]

VBA in excel does not like ";" as delimiter, even if it is your current excel setting. Replace it by comma (",").
(You can add international macro sheet and copy formula here, you will see the proper string for VBA.)

combo

 

[ChristianDK]

Stupid Danish version of formulas in excel
Thanks Combo!!!

 

[xlbo]

The reason it is inserted as text is that you have not told vba the property to which you want to assign this text

The default property for a range is the VALUE property so the text you have will be assigned to the VALUE of the range. You need to assign this to the FORMULA property of a range

Your syntax for a range is also a bit dodgy - syntax should be:

Sheets("Sheetname").Range("Reference")

so

sheets("JusteretEndelig").Range("A1").FORMULA = "Your formula"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

In addition to Geoff's post:
sheets("JusteretEndelig").Range("A1").FormulaLocal = "YourFormulaAsInWorksheet"
(but with specific vba coding of quotation mark).

combo

 

[ChristianDK]

Feedback
My syntax my be a bit rough but as soon as I replaced the ";" with "," (Danish vs English excel formulas.) its all works.

So it's all good and my boss is happy!
So ones again thanks Combo, I owe you one.

 

[xlbo]

Fair enough - didn;t think you could refer to a range like that.....learn something new every day !

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question