Multiplication based on CSV string
 

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

Multiplication based on CSV string
 

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


"Darius Blaszijk" wrote in message
.nl...
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

Multiplication based on CSV string
 

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

"Dana DeLouis" schreef in bericht
...
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


"Darius Blaszijk" wrote in message
.nl...
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

Multiplication based on CSV string
 

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


"Darius Blaszijk" wrote:

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

"Dana DeLouis" schreef in bericht
...
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


"Darius Blaszijk" wrote in message
.nl...
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

Multiplication based on CSV string
 

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

"keepITcool" schreef in bericht
...
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


"Darius Blaszijk" wrote:

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

"Dana DeLouis" schreef in bericht
...
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


"Darius Blaszijk" wrote in message
.nl...
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

Multiplication based on CSV string
 

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.

--
Regards,
Tom Ogilvy




"Darius Blaszijk" wrote in message
.nl...
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

Multiplication based on CSV string
 

Tom,

Exactly what I needed,

thanks alot

Darius Blaszijk

"Tom Ogilvy" schreef in bericht
...
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.

--
Regards,
Tom Ogilvy




"Darius Blaszijk" wrote in message
.nl...
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

Multiplication based on CSV string
 

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


"Darius Blaszijk" wrote in message
.nl...
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

"keepITcool" schreef in bericht
...
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


"Darius Blaszijk" wrote:

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

"Dana DeLouis" schreef in bericht
...
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


"Darius Blaszijk" wrote in message
.nl...
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

Multiplication based on CSV string
 

Oops! Never mind. I just now see Tom's post.

<snip

Dana

Multiplication based on CSV string
 

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

Thank you for helping.

Kind regards, Darius Blaszijk

"Dana DeLouis" schreef in bericht
...
Oops! Never mind. I just now see Tom's post.

<snip

Dana