Hello,

I want to make a userform under excel 2000 which does the following :

1) Allow the user to select a range of cells

2) Click a button to compute :

for j=1 to 5
s(j)=0
for i = 1 to number of selected cells
s(j)=s(j)+ cell(i)^j
next i
next j


The 1) I have done with a RefEdit element and during execution,
I have something like RefEdit1.Value="Sheet1!$A$2:$A$17"

My problem now is to une that value to compute the sums s(j)

Any ideas ?

Thankyou in advance

RR

s(j) = Application.Sum(RefEdit.Value)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RR" wrote in message
...
Hello,

I want to make a userform under excel 2000 which does the following :

1) Allow the user to select a range of cells

2) Click a button to compute :

for j=1 to 5
s(j)=0
for i = 1 to number of selected cells
s(j)=s(j)+ cell(i)^j
next i
next j


The 1) I have done with a RefEdit element and during execution,
I have something like RefEdit1.Value="Sheet1!$A$2:$A$17"

My problem now is to une that value to compute the sums s(j)

Any ideas ?

Thankyou in advance

RR

How about:

Option Explicit
Sub testme()
Dim J As Long
Dim s() As Double
Dim myCell As Range
Dim myRng As Range

J = 5
ReDim s(1 To J)

'use current selection
'set myrng = selection
'or ask
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select some cells", Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Exit Sub
End If

For J = 1 To 5
s(J) = 0
For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
s(J) = s(J) + (.Value ^ J)
End If
End With
Next myCell
Next J
End Sub


RR wrote:

Hello,

I want to make a userform under excel 2000 which does the following :

1) Allow the user to select a range of cells

2) Click a button to compute :

for j=1 to 5
s(j)=0
for i = 1 to number of selected cells
s(j)=s(j)+ cell(i)^j
next i
next j


The 1) I have done with a RefEdit element and during execution,
I have something like RefEdit1.Value="Sheet1!$A$2:$A$17"

My problem now is to une that value to compute the sums s(j)

Any ideas ?

Thankyou in advance

RR

--

Dave Peterson

Thank you for your answer. It helped me understand some things but I
still need more help.

In fact I want the user to select 2 series of data of the same size using
RefEdit elements. For example :

RefEdit1.Value="Sheet1!$A$2:$A$17"
RefEdit2.Value="Sheet1!$B$2:$B$17"

and I want to compute :

1) the sum of Ai * Bi
2) the sum of Ai^2 * Bi
etc...

How can I use the RefEdit1 and RefEdit2 values in the For Each loop you
suggested?

Thanks again

Regards

RR

Dave Peterson wrote in
:

How about:

Option Explicit
Sub testme()
Dim J As Long
Dim s() As Double
Dim myCell As Range
Dim myRng As Range

J = 5
ReDim s(1 To J)

'use current selection
'set myrng = selection
'or ask
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select some cells",
Type:=8) On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Exit Sub
End If

For J = 1 To 5
s(J) = 0
For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
s(J) = s(J) + (.Value ^ J)
End If
End With
Next myCell
Next J
End Sub


RR wrote:

Hello,

I want to make a userform under excel 2000 which does the following :

1) Allow the user to select a range of cells

2) Click a button to compute :

for j=1 to 5
s(j)=0
for i = 1 to number of selected cells
s(j)=s(j)+ cell(i)^j
next i
next j


The 1) I have done with a RefEdit element and during execution,
I have something like RefEdit1.Value="Sheet1!$A$2:$A$17"

My problem now is to une that value to compute the sums s(j)

Any ideas ?

Thankyou in advance

RR

If Range(RefEdit1.Value).Count = Range(RefEdit2.Value).Count Then
MsgBox ActiveSheet.Evaluate("SumProduct(" & _
RefEdit1.Value & "," & RefEdit2.Value & ")")
MsgBox ActiveSheet.Evaluate("SumProduct(--(" & _
RefEdit1.Value & "^2),--(" & RefEdit2.Value & "))")
End If


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RR" wrote in message
...
Thank you for your answer. It helped me understand some things but I
still need more help.

In fact I want the user to select 2 series of data of the same size using
RefEdit elements. For example :

RefEdit1.Value="Sheet1!$A$2:$A$17"
RefEdit2.Value="Sheet1!$B$2:$B$17"

and I want to compute :

1) the sum of Ai * Bi
2) the sum of Ai^2 * Bi
etc...

How can I use the RefEdit1 and RefEdit2 values in the For Each loop you
suggested?

Thanks again

Regards

RR

Dave Peterson wrote in
:

How about:

Option Explicit
Sub testme()
Dim J As Long
Dim s() As Double
Dim myCell As Range
Dim myRng As Range

J = 5
ReDim s(1 To J)

'use current selection
'set myrng = selection
'or ask
Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox(Prompt:="Select some cells",
Type:=8) On Error GoTo 0

If myRng Is Nothing Then
'user hit cancel
Exit Sub
End If

For J = 1 To 5
s(J) = 0
For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
s(J) = s(J) + (.Value ^ J)
End If
End With
Next myCell
Next J
End Sub


RR wrote:

Hello,

I want to make a userform under excel 2000 which does the following :

1) Allow the user to select a range of cells

2) Click a button to compute :

for j=1 to 5
s(j)=0
for i = 1 to number of selected cells
s(j)=s(j)+ cell(i)^j
next i
next j


The 1) I have done with a RefEdit element and during execution,
I have something like RefEdit1.Value="Sheet1!$A$2:$A$17"

My problem now is to une that value to compute the sums s(j)

Any ideas ?

Thankyou in advance

RR