use a macro to insert formulas in a cell

[AnnetteTR]

Hello
I'm about to make a macro where the user enters a lot of data for some rooms in an input box. These data are passed in one line in a spreadsheet. Next room input inserted in the next line, etc.
The problem is that in some columns/cells , I want to put a formula which is based on some of the other inputs. But how should the formula look like in VBA?
I have my input box, where I have a "Sub" for what happens when I press the Save button
Next available number is activated and data is stored with an offset:
With ActiveSync cell
. Offset (0, 22). Value = txtSS1.Text
. Offset (0, 23). Value = txtSS1br.Text
. Offset (0, 24). Value = txtLugeEtMin
. Offset (0, 25). Value = txtLugeEtMax

I want to create a formula where the cell reference for SS1br and LugeEtMin included

I expect to put the formula in with this feature:
'. Offset (0 , 26). Formula = SS1br * LugeEtMin * 0.3 * 3.6<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
But I want the formula to be put in cell .Offset (0 , 26) not the value<o></o>
What should be in the cell is an equation something like: = X4 * Z4 * 0.3 * 3.6
where the 4-figure must be the current row<o></o>

How should the formula look like?

Regards Annette<o></o>

 

[Andrew Poulsom]

Welcome to the Board.

Try:

.Offset(0, 26).FormulaR1C1 = "=RC[-3]*RC[-2]*0.3*3.6"

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[AlphaFrog]

Maybe something like this...

.Offset (0 , 26). Formula = "=" & SS1br & "*" & LugeEtMin & "* 0.3 * 3.6"

 

[AnnetteTR]

Hi Andrew Poulsom

Thank you for your quick response - it works !!

Regards AnnetteTR

 

[AnnetteTR]

Hello again<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

This time I want to create at formula that looks like this<o></o>
=IF(F6*G6<H6;H6;F6*G6)<o></o>
I keep being stopped by a runtime error and an invitation to debug
I put this in my macro:<o></o>
.Offset(0, 9).Formula = "=IF(RC[-4]*RC[-3]<RC[-2];RC[-2];RC[-4]*RC[-3])"<o></o>
Can anybody help?<o></o>

Regards Annette

 

[AnnetteTR]

Oops only half of my "formula" is shown

I put this in my macro:
.Offset(0, 9).Formula = "=IF(RC[-4]*RC[-3]<RC[-2];RC[-2];RC[-4]*RC[-3])"

Regards Annette

 

[AnnetteTR]

Hi
It looks that it get cut off when i submit my post

I will try again
My formula is like this:

.Offset(0, 9).Formula = "=IF(RC[-4]*RC[-3]<RC[-2];RC[-2];RC[-4]*RC[-3])"

Regards Annette

 

[Andrew Poulsom]

Try:

.Offset(0, 9).FormulaR1C1 = "=IF(RC[-4]*RC[-3] < RC[-2],RC[-2],RC[-4]*RC[-3])"<RC[-2],RC[-2],RC[-4]*RC[-3])"< p>

VBA expects commas rather than semicolons and you need to use the FormulaR1C1 property because the formula is in R1c1 style.

 

[AnnetteTR]

Is it impossible to write the "less than" sign in this blog?

my formula is:
.Offset(0, 9).Formula = "=IF(RC[-4]*RC[-3] "less than" RC[-2];RC[-2];RC[-4]*RC[-3])"

Hope it can be shown now.
Regards Annette

 

[AnnetteTR]

Hi Andrew Poulsom

Thank you for your quick response - Ok i forgot the R1C1, and the comma I was not aware of.
Now it works !!

Regards AnnetteTR
<!-- / message -->