Simple Multiplication Formula
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 |
Simple Multiplication Formula
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) "MP" wrote in message ... 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 |
Simple Multiplication Formula
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 On Dec 19, 1:50 pm, "MP" wrote: 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 |
Simple Multiplication Formula
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 :-) "ilia" wrote in message ... 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 On Dec 19, 1:50 pm, "MP" wrote: 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 |
Simple Multiplication Formula
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" wrote in message ... 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) "MP" wrote in message ... I've been searching google for some time now and cant' find the answer This is probably too simple a question |
Simple Multiplication Formula
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" wrote in message ... 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 :-) "ilia" wrote in message ... 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 On Dec 19, 1:50 pm, "MP" wrote: 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 |
Simple Multiplication Formula
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" wrote in message ... 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 |
Simple Multiplication Formula
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" wrote in message ... 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" wrote in message ... 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 |
Simple Multiplication Formula
"Bob Phillips" wrote in message ... 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 |
All times are GMT +1. The time now is 05:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com