Multiplication based on CSV string

D

Darius Blaszijk

Hello,

I want to make a function in VBA that does the following for me.

The data passed to the function is CSV data in the form "1,3,4" and a Range
of cells from a sheet. The result that has to be given back is the
multiplication of cell number 1, 3, 4 (as the CSV data indicates).

I have two questions for you:

- First is there a standard function that does this?? Or is there something
that is similar?? (I can manipulate the CSV format to whatever I want)
- Second if not then is there a method to process a CSV string as desired?
(standard built in)

If both are answered with no I guess I will need to make a CSV processing
function myself.

The CSV data comes from a text file that is automatically read by another
function.

Anybody?

Kind regards, Darius Blaszijk
 
D

Dana DeLouis

The following function returns 12.

Function Apply_Product(s As String)
On Error Resume Next
Apply_Product = Evaluate(Replace("Product(#)", "#", s))
End Function


Sub TestIt()
Debug.Print Apply_Product("1,3,4")
End Sub

HTH
Dana DeLouis
 
D

Darius Blaszijk

Dana,

Sorry for not being too clear in my question. What I'm looking for is this:

CSV = "1,3,4"

On the sheet I have data like this A1 = 2, B1 = 4, C1 = 6, D1 = 8;

The funtion takes this range also as a parameter the type used is Range.

The result shoud be: 2 * 6 * 8 = 96.

Kind regards,

Darius
 
K

keepITcool

Darius .

just use dana's code to think for yourself.
" aha so it's this marvelous PRODUCT() worksheetfunction... "

let's try that in a cell

e1 =PRODUCT(a1:d1)

jeez... it worked.. and you dont need vba





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
D

Darius Blaszijk

keep ITcool,

It's still not what I mean ... I think.

From a1 to d1 I have the values 2,4,6,8 so cell nr 1 is 2 and cell nr 4 is
8.
I have a sting tha say's 2,2,3. This means that cell nr 2 and cell nr 2 and
cell nr 3 have to be multiplied with themselves and returned as a value.

So in this case it would mean the result value is : 4 * 4 * 6 = 96.

For the CSV value; I don't know in advance the number of parameters. It can
be 3 it can be 10. Also the order in which the values (cell numbers) are in
is not predictable.

Hope this helps.

Kind regards, Darius Blaszijk
 
T

Tom Ogilvy

You mean you would have a function that takes arguments such as

SomeFunction(A1:A10,"1,3,4")

and you want
A1*A3*A4

(the multiplication actually performed)

If you have Excel 2000 or later

Public Function SomeFunction(rng As Range, sStr As String)
Dim varr, ans
Dim i As Long
varr = Split(sStr, ",")
ans = 1
For i = LBound(varr) To UBound(varr)
Next
If rng.Columns.Count > 1 And rng.Rows.Count > 1 _
Or rng.Areas.Count > 1 Then
ans = "Invalid range"
Exit Function
ElseIf rng.Columns.Count > 1 Then
For i = LBound(varr) To UBound(varr)
ans = ans * rng.Offset(0, varr(i) - 1)(1).Value
Next i
ElseIf rng.Rows.Count > 1 Then
For i = LBound(varr) To UBound(varr)
ans = ans * rng(varr(i), 1).Value
Next
Else
ans = rng(1).Value
End If
SomeFunction = ans
End Function

usage
in worksheet:
=SomeFunction(A5:A10,"1,3,4")

in VBA
Sub AAAA()
Dim sStr As String
Dim rng As Range
sStr = "1,3,4"
Set rng = Range("A5:A10")
Debug.Print SomeFunction(rng, sStr)
End Sub


would get you started. It has very little error checking, so you would need
to add that. I have limited it to a contiguous range of cells that are
either one row tall or 1 column wide.
 
D

Dana DeLouis

Not sure, but would this work? It returned 96.

Function MyProduct(s As String, rng As Range) As Double
Dim v
Dim j As Long
Dim Ans As Double
Ans = 1
v = Split(s, ",")
On Error Resume Next
For j = LBound(v) To UBound(v)
Ans = Ans * rng(v(j))
Next
MyProduct = Ans
End Function


Sub TestIt()
'Set up
[A1:D1].Formula = "=2*Column()" ' 2,4,6,8

Debug.Print MyProduct("2,2,3", Range("A1:D1"))
End Sub

HTH
Dana DeLouis
 
D

Darius Blaszijk

Indeed Tom sugested the same sollution. Split was what I was looking for.

Thank you for helping.

Kind regards, Darius Blaszijk
 
Top