Solved: Formulas, entered by macro

[vosmerkin]

I had this problem long ago with formulas.
Now I have it again with dates.

I'm assigning a date (or formula) to a cell, but Excel doesn't see it like date/formula. To make it work i have to enter the formula line and press Enter key. After that formula starts to calculate and date becomes a date value and I can formate it like "April, 2007" or 01.01.2007, etc.

I've tried to assign .Value property, to .Formula property
Nothing helps

[Simon Lloyd]

Vosmerkin, you need to provide the portion of code that you are using however you can format a date like:
[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Format(Range("A1").Value, "dd mmm yyyy")
End Sub
[/VBA]if you were to use this and enter 22 12 07 in A1 the cell will display 22-Dec-2007 however if you entered it as a serial number 221207 (the number of days since 1900) then the cell will display 22-Aug-05.

Hope this helps!

Regards,
Simon

[mdmackillop]

or
[VBA] Range("A:A").NumberFormat = "dd.mm.yy"
[/VBA]

[Bob Phillips]

try storing the value in a date variable in VBA.

[vosmerkin]

Now it's fine with dates.
[VBA]CDate("01.01.2007")[/VBA]
This works.

But what to do with formulas?
[VBA]Dim d As Variant
d = "=сумм(A42+A41)"
Range("a40").Select
ActiveCell.Formula = d

End Sub[/VBA]
I get error in cell A40. To make formula work I have to go to the formula line and press ENTER key

[Bob Phillips]

Is this a udf, or a procedure? And what is cymm function in English?

[vosmerkin]

Sorry
[vba]Sub macro1()

Dim d As Variant
d = "=summ(A42:A41)"
Range("a40").Select
ActiveCell.Formula = d

End Sub[/vba]

[Charlize]

[vba]Sub macro_sum()
Range("A40").Formula = "=SUM(A41:A42)"
End Sub
[/vba]The formula gets translated into the local excel version.

Mine is a dutch version so SUM becomes SOM. But you'll have to watch out with , or ; when you need to use these in a formula or conditional formatting.

Charlize

[vosmerkin]

[vba]Sub macro_sum()
Range("A40").Formula = "=SUM(A41:A42)"
End Sub
[/vba]The formula gets translated into the local excel version.

Mine is a dutch version so SUM becomes SOM. But you'll have to watch out with , or ; when you need to use these.

Charlize

It works. Thanks a lot.