How do I...

[Paul Hyett]

Hi,

I wonder if you could help?

How do I go about taking data from a succession of cells in one
spreadsheet, inserting them one at a time into a formula in another
spreadsheet, and then putting the result of each calculation back into
the original spreadsheet in cells I specify?

Regards,
--
Paul Hyett, Cheltenham

[Norman Jones]

Hi Paul,

Perhaps you could adopt an approach like this:

'======================>>
Public Sub TestIt()

Dim RngInput As Range
Dim RngOutput As Range
Dim i As Long
Dim v As Double

Set RngInput = Sheets("Sheet3").Range("A1:A10")
Set RngOutput = Sheets("Sheet3").Range("C1:C10")

For i = 1 To RngInput.Cells.Count
If IsNumeric(RngInput(i)) Then
v = RngInput(i).Value
RngOutput(i) = v * (Application.Pi ^ 2)
End If
Next i

End Sub
'<<======================

---
Regards,
Norman



"Paul Hyett" <pah@nojunkmailplease.co.uk> wrote in message
news:ggEi+IALjI9CFw8v@activist.demon.co.uk...
> Hi,
>
> I wonder if you could help?
>
> How do I go about taking data from a succession of cells in one
> spreadsheet, inserting them one at a time into a formula in another
> spreadsheet, and then putting the result of each calculation back into
> the original spreadsheet in cells I specify?
>
> Regards,
> --
> Paul Hyett, Cheltenham

[Paul Hyett]

In microsoft.public.excel.misc on Sat, 6 Aug 2005 at 11:29:51, Norman
Jones wrote :
>Hi Paul,
>
>Perhaps you could adopt an approach like this:
>
>'======================>>
>Public Sub TestIt()
>
> Dim RngInput As Range
> Dim RngOutput As Range
> Dim i As Long
> Dim v As Double
>
> Set RngInput = Sheets("Sheet3").Range("A1:A10")
> Set RngOutput = Sheets("Sheet3").Range("C1:C10")
>
> For i = 1 To RngInput.Cells.Count
> If IsNumeric(RngInput(i)) Then
> v = RngInput(i).Value
> RngOutput(i) = v * (Application.Pi ^ 2)
> End If
> Next i
>
>End Sub
>'<<======================
>
Thanks for this, but I don't follow some of it - are 'Long' and 'Double'
worksheet functions?

I'm using Excel 2002 and I don't recognise them.
--
Paul Hyett, Cheltenham

[Norman Jones]

Hi Paul,

My suggestion was to use a VBA procedure to acieve your objective.

> Thanks for this, but I don't follow some of it - are 'Long' and 'Double'
> worksheet functions?

Long and Double are data types that are declared for two of the procedure's
variables.

If You are not familiar with macros (aka procedures), you may wish to visit
David McRitchie's 'Getting Started With Macros And User Defined Functions'
at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman



"Paul Hyett" <pah@nojunkmailplease.co.uk> wrote in message
news:o6PUseAfjQ9CFw81@activist.demon.co.uk...
> In microsoft.public.excel.misc on Sat, 6 Aug 2005 at 11:29:51, Norman
> Jones wrote :
>>Hi Paul,
>>
>>Perhaps you could adopt an approach like this:
>>
>>'======================>>
>>Public Sub TestIt()
>>
>> Dim RngInput As Range
>> Dim RngOutput As Range
>> Dim i As Long
>> Dim v As Double
>>
>> Set RngInput = Sheets("Sheet3").Range("A1:A10")
>> Set RngOutput = Sheets("Sheet3").Range("C1:C10")
>>
>> For i = 1 To RngInput.Cells.Count
>> If IsNumeric(RngInput(i)) Then
>> v = RngInput(i).Value
>> RngOutput(i) = v * (Application.Pi ^ 2)
>> End If
>> Next i
>>
>>End Sub
>>'<<======================
>>
> Thanks for this, but I don't follow some of it - are 'Long' and 'Double'
> worksheet functions?
>
> I'm using Excel 2002 and I don't recognise them.
> --
> Paul Hyett, Cheltenham

[Paul Hyett]

In microsoft.public.excel.misc on Sat, 6 Aug 2005 at 20:49:39, Norman
Jones wrote :
>Hi Paul,
>
>My suggestion was to use a VBA procedure to acieve your objective.

Oh, right.

Unfortunately I've never used it.
>
>If You are not familiar with macros (aka procedures)

I've used them occasionally, but I can't pretend to be proficient with
them.
--
Paul Hyett, Cheltenham

[Norman Jones]

Hi Paul,

> I've used them occasionally, but I can't pretend to be proficient with
> them.

you may wish to visit David McRitchie's 'Getting Started With Macros And
User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


If you would like the example macro adapted to perfom your desired tasks,
post back with the following information:

(1) The name of the two worksheets
(2) The address of the initial data cells
(3) The exact formula that you are feeding the value into
(4) The address of the cells which are to receive the calculated results.


---
Regards,
Norman



"Paul Hyett" <pah@nojunkmailplease.co.uk> wrote in message
news:AAE+SgCwka9CFw6X@activist.demon.co.uk...
> In microsoft.public.excel.misc on Sat, 6 Aug 2005 at 20:49:39, Norman
> Jones wrote :
>>Hi Paul,
>>
>>My suggestion was to use a VBA procedure to acieve your objective.
>
> Oh, right.
>
> Unfortunately I've never used it.
>>
>>If You are not familiar with macros (aka procedures)
>
> I've used them occasionally, but I can't pretend to be proficient with
> them.
> --
> Paul Hyett, Cheltenham