That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

Why doesn't it help you much Mark?

You asked how to pick up a value from a spread sheet.

you refer to a value on a spreadsheet with

Worksheets("sheet1").Range("b1").Value

you put a value in a spreadsheet with

Worksheets("sheet1").Range("B7").Value = 21


you can store the values in a variable.

vVal = Worksheets("sheet1").Range("b1").Value

Worksheets("sheet1").Range("b1").Value = vVal ^ 2

Not sure what else you would need to know.

--
Regards,
Tom Ogilvy


mark_varney47 wrote in message
...
That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

VBA can "pick up" and "put back" spreadsheet data in several ways. A
few among them:

You can refer to the cells directly:

Sheets("Sheet1").Range("B7").Value = _
Sheets("Sheet1").Range("B1") + Sheets("Sheet1").Range("B2")

which can be shortened to

With Sheets("Sheet1")
.Range("B7").Value = .Range("B1").Value + .Range("B2").Value
End With

or, if Sheet1 is the activesheet, just

Range("B7").Value = Range("B1").Value + Range("B2").Value

You can assign intermediate variables:

Dim myVar1 As Double
Dim myVar2 As Double
Dim myVar3 As Double

myVar1 = Sheets("Sheet1").Range("B1")
myVar2 = Sheets("Sheet1").Range("B2")
myVar3 = myVar1 + myVar2
Sheets("Sheet1").Range("B7").Value = myVar3

You can assign object variables to refer to the cells:

Dim myCell1 As Range
Dim myCell2 As Range
Dim myCell3 As Range
Set myCell1 = Sheets("Sheet1").Range("B1")
Set myCell2 = Sheets("Sheet1").Range("B2")
Set myCell3 = Sheets("Sheet1").Range("B7")
myCell3.Value = myCell1.Value + myCell2.Value

You can also capture a contiguous range of data into a Variant
variable with a 2-D array:

Dim myRng As Variant
myRng = Sheets("Sheet1").Range("B1:B6").Value
Sheets("Sheet1").Range("B7").Value = _
myRng(1, 1) + myRng(2, 1) - myRng(6, 1)

In article ,
mark_varney47 wrote:

That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

post yer code.
"mark_varney47" wrote in message
...
That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

Bradley/Tom or anyone else that can help,
This is the code that I am trying to link to my spreadsheet.

Public Function Range("B1").Value As String,Range("B2"). _
Value As Double,Range("B3").Value As Double,Range("B4"). _
Value As Double,Range("B5").Value As Double,Range("B6"). _
Value As Double) As Double

Dim1 d1 As Double, d2 As Double
d1=(Log(B2/B3)+(B6^2/2)*B4)/(B6*Sqr(B4))
D2=(D1-B6*Sqr(B4)

If B1="c" Then _
B7=Exp(-B5*B4)*(B2*CND(d1)-B3*CND(d2))
If B1="p" Then _
B7=Exp(-B5*B4)*(B3*CND(-d2)-B2*CND(-d1) _
ElseIf _

End Function

The values to be input into the spreadsheet are as follows:
B1=c
B2=3900
B3=3900
B4=0.5
B5=0.05
B6=0.25
B7=Blank as this is where the output should appear.


Any help with this would be gratefully appreciated.
Thanks,
Mark

Can anyone help me with this spreadsheet/VBA problem???
Many Thanks,
Mark Varney