Putting multiplication formula in cells with vba

Hi everyone, I am sure this is very simple, but I cant figure out how to do it I hope someone can help.

I want to put a simple multiplication formula into a cell....for example:

Please Login or Register  to view this content.

.......that is straightforward and works fine, however I need to use variables for the row number of the cells being multiplied so what I really want to do (but of course this syntax won't work) is:

Please Login or Register  to view this content.

...where 'start' and 'finish' are variables (integers) reperesenting row numbers......

Please can anyone tell me how I make the statement between the quotes work so that the column letter and variables can be combined into cell references to be multiplied and will work between quotes to become the literal contents of the cell?

thanks

bj

Would you please edit your post to add code tags?

well, then something like this

Please Login or Register  to view this content.

How would you trigger the sub()? If it's triggered from a button or menu, where would it get Start and Finish? If it's called from another sub(), where does that sub() get Start and Finish? Maybe input boxes?

Also, how would it know what the destination cell is? If it's called from WorksheetChange(), than I'm back to - where does it get Start and Finish?

As far as I'm concerned this is about the syntax of this particular statement and not about the whole macro.

Nothing wrong with it. Maybe the OP didn't know how to call the Sub() and tried to run it, which failed.

why make it so complicated?

bj, using foxguy's code in the way presented earlier, you need to set three variables in your code:
- the range where you want to put the formula, for example dim MyCell as range
- the first row number (dim start as Long)
- the second row number (dim finish as Long)

In your code, you assign values to these variables, e.g.

Please Login or Register  to view this content.

Then you call the Sub Foxguy provided, using this syntax

Please Login or Register  to view this content.

The Sub will then take these variable's values and assign them to the variables in the Sub.

Please Login or Register  to view this content.

rCell will be = MyRange
lStart will be = start
lEnd will be = finish

Then the values will be plugged into the respective places.

Does that help you understand this a bit better?

cheers

teylyn;

I get the feeling that bj is real new to VBA. I can't imagine that (s)he wants to have MyRange, Start, & Finish hard coded into a sub(). Obviously that would only run once, and never be used again without re-coding the sub(). (S)he must have more than one cell to enter a formula into, and is looking for a way to have many formulas entered into many cells, otherwise (s)he would just manually enter the formula.

I gathered that (s)he was asking how to enter this info

Please Login or Register  to view this content.

without hard coding it into a sub()

Or maybe I misunderstood what (s)he is looking for.

Foxguy,

my interpretation is that bj was having trouble running the sub with the parameters and did not know how to call a sub that requires parameters.

Of course, hard-coding the variables in my sample code was only a means of giving them a value. I'm sure bj will know how to assign different values to those variables, and if not, we'll probably see another thread here soon.

Since bj has marked the thread as solved after my last post, I assume s/he has an answer that satisfies the question, whatever it may have been.

Please Login or Register  to view this content.

That syntax is fine if my_variable is an integer (meaning whole number; it should be declared as a Long) variable between 1 and the number of rows. There's an implicit type conversion, but omitting it isn't a cardinal sin:

Please Login or Register  to view this content.

The catenation is unnecessary, though:

Please Login or Register  to view this content.

Many thanks for your patience, I have it all working now