Simple Multiplication Formula

[MP]

I've been searching google for some time now and cant' find the answer
This is probably too simple a question

I'm trying to write a function that will put a formula in ResultCol to
multiply InputColOne times InputColTwo

Public Function MultiplyCells(RowIdx As Long, ColOne As Long, ColTwo As
Long, ResultCol As Long, oWs As Worksheet) As Boolean
oWs.Cells(RowIdx, ResultCol).FormulaR1C1 = oWs.Cells(RowIdx, ColOne).Value
* oWs.Cells(RowIdx, ColTwo).Value
End Function

I'm sure my syntax is wrong, I don't clearly understand the .FormumaR1C1
method...
It's not throwing an error, but it's putting the value in the result cell,
not the formula

any hints?
Thanks Mark

 

[Bob Phillips]

Just use Value not FormulaR1C1.

Where is this being called from, another macro? If a worksheet, you cannot
pass a worksheet object. You could pass its name but not the worksheet.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ilia]

You want it to be like this, perhaps?

Public Function MultiplyCells(RowIdx As Long, _
ColOne As Long, _
ColTwo As Long, _
ResultCol As Long, _
oWs As Worksheet) As Boolean
With oWs
.Cells(RowIdx, ResultCol).Formula = "=" & _
.Cells(RowIdx, ColOne).Address & "*" & _
.Cells(RowIdx, ColTwo).Address
End With
End Function

 

[MP]

Fantastic!!!
Thank you so much for that prompt reply
That works beautifully!

It produces an "absolute" reference
example:
=$C$21*$D$21
which is fine for my current application....

just for my education, how would I change it to produce a "relative"
reference
=C21*D21
?

also is there a place in excel help where I should have been able to find
this?
or other good links for programatic object model info

Thanks again for the solution
Mark

 

[MP]

Thanks Bob,
It is in a class I'm writing to wrap the excel object
Called from other vba environments or vb6

like Call oExcel.MultiplyCells(Row, Col1, Col2, ColResult, oWs)

I'll try the value property as you suggest.

also Ilia posted a solution using .Formula which also works well.

Thanks
Mark

 

[Bob Phillips]

But it is a value not a formula. Formula might work, but Value helps explain
the intent

Public Function MultiplyCells(RowIdx As Long, _
ColOne As Long, _
ColTwo As Long, _
ResultCol As Long, _
oWs As Worksheet) As Boolean
With oWs
.Cells(RowIdx, ResultCol).Value= "=" & _
.Cells(RowIdx, ColOne).Address(False, False) & "*" & _
.Cells(RowIdx, ColTwo).Address(False, False)
End With
End Function

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MP]

Hi Bob,
Guess since I'm new to excel, I don't have the terminology straight...
I thought my intent was to place a formula into the cell.

I was thinking when I put in a cell (for example manually type the
following) "= somecelladdress * othercelladdress" that I was entering a
formula...?

....because I can then copy that cell content, and paste special...

if I select Formula I get the "formula" (adjusted per row/col)
if I select Value, I get the resultant value of the formula, but not the
formula itself.

So maybe I'm confusing the terms somehow???

Any way, both .Value and .Formula appear to place the same 'contents' into
the target cell, which is exactly what i'm looking for.

So thanks again to both of you for your inputs.
Mark

 

[Bob Phillips]

Hi MP,

No it is not you it is me. You started by putting a value in the cell but
switched halfway along, and I missed that point. Sorry about that.

You are absolutely right, you are inserting a formula, not a value. And even
though Value and Formula both work, in my philosophy I would suggest you
therefore use Formula not Value as it signifies the code's intent as well as
working.

And hopefully, I gave you the relative cell addresses as you wanted

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MP]

Hi MP,

No it is not you it is me. You started by putting a value in the cell but
switched halfway along, and I missed that point. Sorry about that.
You are absolutely right, you are inserting a formula, not a value. And
even though Value and Formula both work, in my philosophy I would suggest
you therefore use Formula not Value as it signifies the code's intent as
well as working.

makes sense, thanks!

And hopefully, I gave you the relative cell addresses as you wanted

ah Ha!!! I missed that one the first time ! (False,False)...I get it
Thanks very much!

Mark