View Full Version : Solved: Formulas, entered by macro
vosmerkin
04-18-2007, 11:10 PM
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
04-18-2007, 11:39 PM
Vosmerkin, you need to provide the portion of code that you are using however you can format a date like:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Format(Range("A1").Value, "dd mmm yyyy")
End Sub
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
04-19-2007, 12:15 AM
or
Range("A:A").NumberFormat = "dd.mm.yy"
Bob Phillips
04-19-2007, 12:58 AM
try storing the value in a date variable in VBA.
vosmerkin
04-19-2007, 01:04 AM
Now it's fine with dates.
CDate("01.01.2007")
This works.
But what to do with formulas?
Dim d As Variant
d = "=сумм(A42+A41)"
Range("a40").Select
ActiveCell.Formula = d
End Sub
I get error in cell A40. To make formula work I have to go to the formula line and press ENTER key
Bob Phillips
04-19-2007, 01:10 AM
Is this a udf, or a procedure? And what is cymm function in English?
vosmerkin
04-19-2007, 01:39 AM
Sorry
Sub macro1()
Dim d As Variant
d = "=summ(A42:A41)"
Range("a40").Select
ActiveCell.Formula = d
End Sub
Charlize
04-19-2007, 01:49 AM
Sub macro_sum()
Range("A40").Formula = "=SUM(A41:A42)"
End Sub
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
04-19-2007, 01:55 AM
Sub macro_sum()
Range("A40").Formula = "=SUM(A41:A42)"
End Sub
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.