How do I insert a formula

[Regouin]

Hello everyone,

I Have a little problem, with the macro I am running finally it selects a row, inserts a row at that point and then i want it to insert some excel formulas in the cell.

in example:

5 10 A1+A2

now when I use

.Range("D" & rij).FormulaR1C1 = "=datum(jaar(B" & rij & ");maand(B" & rij & ")+1;dag(B" & rij & "))"
.Range("F" & rij).FormulaR1C1 = "=D" & rij & "-7"
.Range("H" & rij).FormulaR1C1 = "=als(vandaag()>=F" & rij & ";x; )"

it goes absolute ape, the first input generates an error, upon ignoring the first the second does insert and does give the right formula but it looks like this ='d855'-7 so excel doesnt see it as a proper formula. And the last formula has the problem that it has text insertions, so more "" then vba can handle, I can work around this by inserting the text in designated excel sheet, but I dont understand where the first error is coming from.

tia
frank


I have a dutch excel

datum = date
jaar = year
rij = the row number (vba number)
maand = month
dag = day
als = if
vandaag = today

[johnske]

Hi Regouin,

I'm having difficulty with the language here so can't help much, but one thing I did notice was the "If" statement in the last line i.e.

=als(vandaag()>=F" & rij & ";x; )

which, in English would be

=If(Today()>=F" & rij & ";x; )

The worksheet If-Then-Else statement usually has the syntax =If({condition is met},,). The first comma is equivalent to "Then" and the second comma is equivalent to "Else" i.e. =If({condition is met},{(then) do something},{(else) do another thing}).

HTH a bit,
John

[Regouin]

hi,

i edited it a bit and I fixed the second input problem, but it still generates an error on the first and last formula, I am not getting how or why this is happening.

.Range("D" & rij).Formula = "=datum(jaar(B" & rij & ");maand(B" & rij & ")+1;dag(B" & rij & "))"
.Range("F" & rij).Formula = "=D" & rij & "-7"
.Range("H" & rij).Formula = "=als(vandaag()>=F" & rij & ";""x"";"""")"

I am not quite getting what you mean with the if statement, is it just that it should insert comma's instead of point-comma? Because the if formula is supposed to show an X when today's date is past F & rij and must remain blank if it isnt.

tia
frank


[EDIT]

ok, changing the point-comma's to comma's does insert the formulas in excel, funny thing though that it automatically converts the comma's to point-comma's when they are inserted in excel.
Now the problem is that it generates a name error in the cell upon insertion. When I manually remove the "=" and put it back it calculates all of a sudden. I am really not following where this is coming from.


[/EDIT]

[johnske]

Well, what I meant is that it's usually put in the worksheet as something like this

=IF(TODAY()>=F9,"x","" )

[Regouin]

For some reason I need to tell VBA to insert comma's and when it gets to excel it transforms them mysteriously into point-comma's, but since that is what it is meant to be I dont worry about that anymore. The problem I have now is that it sees the entire formula as a word and giving me the name error until I manually remove and insert the = sign at the start.
I now figured that it is not so much as removing and inserting the = sign but that editing the formula on the formula bar and then immediately pressing enter is enough to fix the name error. (so F2 + enter)

frank

[johnske]

Ok, but I don't know where the point commas (semi-colons) are coming from, this runs fine on my machine and comes up as commas on the worksheet

Sub Try()
      Range("b1").Formula = "=IF(TODAY()>=F9,""x"","""" )"
End Sub

[TonyJollans]

Hi Frank,

Firstly, your punt-commas (semi-colons in English) are correct - assuming your international settings are Dutch. Also, your If without an explicit Else should work - but will return FALSE when the condition is not met and as you want blank your amended version is better.

Secondly I'm afraid I don't understand what the problem is. I don't know the Dutch Excel but I have done a literal translation (and replaced semicolons with commas) to get this in English:

.Range("D" & rij).Formula = "=date(year(B" & rij & "),month(B" & rij & ")+1,day(B" & rij & "))"
.Range("F" & rij).Formula = "=D" & rij & "-7"
.Range("H" & rij).Formula = "=if(today()>=F" & rij & ",""x"","""")"

and it works fine for me. Obviously I have just used a random value for rij - and wrapped the code in a With ActiveSheet ... End With construct.

Could you expand a little on what the problem is?

[Regouin]

Hi tony,

Your translation is perfect, that is exactly what it is supposed to do. Now I am going to try to explain what it does.

upon selecting an interval in a combobox (monthly, quarterly, yearly, etc) it inserts a row on the first blank cell under the header monthly, quarterly, etc. So you get a clean row for the inserted task. I then insert the name of task (via an inputbox) in Column A in the newly inserted row (say row 58), then it inserts today's date in B58, a certain number in C58, now it comes to the insertion of the 3 formulas, the weird thing is that it thinks that formula 1 and 3 are text and it gives a Name error, formula 2 it inserts just fine.

To solve the name error I can just edit the formula (pressing F2) and then hit enter, that it is enough. Now is there a VBA method to recalculate the cell or something similar, because that might work.

[EDIT]

Ok guys I solved it, stupid language problem, since VBA is in english and my excel is in dutch it does weird things every now and then. I changed the formulas to their english twins and now it works beautifully, when I return to excel it even translated the formulas into dutch!
This also explained why I was the only one encountering these problems.
Thanx for your help.
frank

frank


[/EDIT]