Product (multiplication of cells)

**Mbrolass** · 05-01-2007, 11:52 AM

Hi,

Hopefully this is an easy one...

I'm looking to calculate the product of a nymber of consecutive cells in a particular row using VB.

Example.

I have, say, 5 variables listed in row 2 and I wish to find the product of these variables in cells B6 to B10 and produce the answer in cell B11

i.e. cell B11 = B6*B7*B8*B9*B10

In this example this is easy, however the problem is that I don't know how many variables I'll have, I only know the starting cell of the product will be in cell B(number of variables + 1) (e.g. B6 if 5 variables, B4 if 3 variables, etc.) and the answer will always be in cell B(2*number of variables + 1) (e.g. B11 if 5 variables, B7 if 3 variables).

Can this be done with a simple command or will a loop be needed?

Thanks,

Mbrolass.

**sweep** · 05-01-2007, 12:15 PM

Hi,

Apologies if I'm missing your point here, but can't you use the worksheet function =PRODUCT()?

**Mbrolass** · 05-01-2007, 12:23 PM

I'm not actually doing the calculation in an excel spreadsheet and I've tried to find "product" in the visual basic commands but can not find it.

I need to use it in a VB procedure and am not sure how.

I'm new to VB so it may seem a silly question...

**sweep** · 05-01-2007, 12:28 PM

Here ya go

Sub mult()
Dim answer As Single

Range("A1:A3").Select
answer = Application.WorksheetFunction.Product(UsedRange)
Cells(1, 4).Value = answer
End Sub

**Mbrolass** · 05-01-2007, 01:15 PM

That's great. Thanks

Product (multiplication of cells)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Product (multiplication of cells)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions