use a macro to insert formulas in a cell

AnnetteTR

Board Regular
Joined
Aug 19, 2010
Messages
83
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
But I want the formula to be put in cell .Offset (0 , 26) not the value<o:p></o:p>
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:p></o:p>


How should the formula look like?

Regards Annette
<o:p></o:p>
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

Try:

Code:
.Offset(0, 26).FormulaR1C1 = "=RC[-3]*RC[-2]*0.3*3.6"
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Maybe something like this...

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

AnnetteTR

Board Regular
Joined
Aug 19, 2010
Messages
83
Hi Andrew Poulsom

Thank you for your quick response - it works !!

Regards AnnetteTR
 

AnnetteTR

Board Regular
Joined
Aug 19, 2010
Messages
83
Hello again<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

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

Regards Annette
 

AnnetteTR

Board Regular
Joined
Aug 19, 2010
Messages
83
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

Board Regular
Joined
Aug 19, 2010
Messages
83
Hi
It looks that it get cut off when i submit my post

I will try again
My formula is like this:
Code:
.Offset(0, 9).Formula = "=IF(RC[-4]*RC[-3]<RC[-2];RC[-2];RC[-4]*RC[-3])"
Regards Annette
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
.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

Board Regular
Joined
Aug 19, 2010
Messages
83
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

Board Regular
Joined
Aug 19, 2010
Messages
83
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 -->
 

Forum statistics

Threads
1,081,748
Messages
5,361,041
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top