I have a stock project that I have been working on and I
am trying to optimize some of my code and was looking for
some help. Basically what happens is that at then end of
the day a script runs that gets the current price for a
stock (current price already in the workbook) and runs
another script to paste that data as static into the
history sheet.
Here is a sample of the code
'SBC End of Day
Application.Goto Reference:="SBC_CurPrice"
Selection.Copy
Application.Goto Reference:="SBC_Data"
StaticPriceSet

'MSFT End of Day
Application.Goto Reference:="MSFT_CurPrice"
Selection.Copy
Application.Goto Reference:="MSFT_Data"
StaticPriceSet

I would like to know how to do a for loop were it will
loop through a defined range and add in the references
and then go to the next step.

Thanks

For Each cell In Range("A1:H10")
cell.Copy Destination:= cell.Offset(100,0)
Next cell

as an example

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Justin Ragsdale" wrote in message
...
I have a stock project that I have been working on and I
am trying to optimize some of my code and was looking for
some help. Basically what happens is that at then end of
the day a script runs that gets the current price for a
stock (current price already in the workbook) and runs
another script to paste that data as static into the
history sheet.
Here is a sample of the code
'SBC End of Day
Application.Goto Reference:="SBC_CurPrice"
Selection.Copy
Application.Goto Reference:="SBC_Data"
StaticPriceSet

'MSFT End of Day
Application.Goto Reference:="MSFT_CurPrice"
Selection.Copy
Application.Goto Reference:="MSFT_Data"
StaticPriceSet

I would like to know how to do a for loop were it will
loop through a defined range and add in the references
and then go to the next step.

Thanks

Don't know what staticpriceset is but maybe this would be better. NO
selecting or copying.
range("sbc_data")=range("sbc_curr")


--
Don Guillett
SalesAid Software

"Justin Ragsdale" wrote in message
...
I have a stock project that I have been working on and I
am trying to optimize some of my code and was looking for
some help. Basically what happens is that at then end of
the day a script runs that gets the current price for a
stock (current price already in the workbook) and runs
another script to paste that data as static into the
history sheet.
Here is a sample of the code
'SBC End of Day
Application.Goto Reference:="SBC_CurPrice"
Selection.Copy
Application.Goto Reference:="SBC_Data"
StaticPriceSet

'MSFT End of Day
Application.Goto Reference:="MSFT_CurPrice"
Selection.Copy
Application.Goto Reference:="MSFT_Data"
StaticPriceSet

I would like to know how to do a for loop were it will
loop through a defined range and add in the references
and then go to the next step.

Thanks

varr = ("SBC", MSFT")
for i = lbound(varr) to ubound(varr)
Range(varr(i) & "_Data").Value = range(varr(i) & "_CurPrice").Value
Next

Not sure what you mean by "add in the references", so that is my guess on
what you want.

--
Regards,
Tom Ogilvy

"Justin Ragsdale" wrote in message
...
I have a stock project that I have been working on and I
am trying to optimize some of my code and was looking for
some help. Basically what happens is that at then end of
the day a script runs that gets the current price for a
stock (current price already in the workbook) and runs
another script to paste that data as static into the
history sheet.
Here is a sample of the code
'SBC End of Day
Application.Goto Reference:="SBC_CurPrice"
Selection.Copy
Application.Goto Reference:="SBC_Data"
StaticPriceSet

'MSFT End of Day
Application.Goto Reference:="MSFT_CurPrice"
Selection.Copy
Application.Goto Reference:="MSFT_Data"
StaticPriceSet

I would like to know how to do a for loop were it will
loop through a defined range and add in the references
and then go to the next step.

Thanks

That does help a little, I have actually adjusted the
code a little to make it easier to explain
Basically i need create a for loop to add symbols into XXX
and then run another program StaticPriceSet.

I have tred to create code to add the symbol into the
string but it doesn't seem to work with this function.

Application.Goto Reference:="XXX_Data" 'Loop through
putting symbols into the XXX that are in a list.

StaticPriceSet

-----Original Message-----
varr = ("SBC", MSFT")
for i = lbound(varr) to ubound(varr)
Range(varr(i) & "_Data").Value = range(varr(i)
& "_CurPrice").Value
Next

Not sure what you mean by "add in the references", so
that is my guess on
what you want.

--
Regards,
Tom Ogilvy

"Justin Ragsdale" wrote in message
...
I have a stock project that I have been working on and
I
am trying to optimize some of my code and was looking
for
some help. Basically what happens is that at then end
of
the day a script runs that gets the current price for a
stock (current price already in the workbook) and runs
another script to paste that data as static into the
history sheet.
Here is a sample of the code
'SBC End of Day
Application.Goto Reference:="SBC_CurPrice"
Selection.Copy
Application.Goto Reference:="SBC_Data"
StaticPriceSet

'MSFT End of Day
Application.Goto Reference:="MSFT_CurPrice"
Selection.Copy
Application.Goto Reference:="MSFT_Data"
StaticPriceSet

I would like to know how to do a for loop were it will
loop through a defined range and add in the references
and then go to the next step.

Thanks


.

Sub Tester1AA()
varr = Array("SBC", "MSFT")
For i = LBound(varr) To UBound(varr)
Application.Goto Reference:=Range(varr(i) & "_Data")
' do something
MsgBox "..."
Next

End Sub

Worked fine for me. If you put this in a sheet module, and the Range is on
another sheet, you will need to preface the range with a reference to the
sheet

Private Sub Commandbutton1_Click()
varr = Array("SBC", "MSFT")
For i = LBound(varr) To UBound(varr)
Application.Goto Reference:=Worksheets("Data").Range(varr(i) & "_Data")
' do something
MsgBox "..."
Next

End Sub

--
Regards,
Tom Ogilvy


"Justin Ragsdale" wrote in message
...
That does help a little, I have actually adjusted the
code a little to make it easier to explain
Basically i need create a for loop to add symbols into XXX
and then run another program StaticPriceSet.

I have tred to create code to add the symbol into the
string but it doesn't seem to work with this function.

Application.Goto Reference:="XXX_Data" 'Loop through
putting symbols into the XXX that are in a list.

StaticPriceSet

-----Original Message-----
varr = ("SBC", MSFT")
for i = lbound(varr) to ubound(varr)
Range(varr(i) & "_Data").Value = range(varr(i)
& "_CurPrice").Value
Next

Not sure what you mean by "add in the references", so
that is my guess on
what you want.

--
Regards,
Tom Ogilvy

"Justin Ragsdale" wrote in message
...
I have a stock project that I have been working on and
I
am trying to optimize some of my code and was looking
for
some help. Basically what happens is that at then end
of
the day a script runs that gets the current price for a
stock (current price already in the workbook) and runs
another script to paste that data as static into the
history sheet.
Here is a sample of the code
'SBC End of Day
Application.Goto Reference:="SBC_CurPrice"
Selection.Copy
Application.Goto Reference:="SBC_Data"
StaticPriceSet

'MSFT End of Day
Application.Goto Reference:="MSFT_CurPrice"
Selection.Copy
Application.Goto Reference:="MSFT_Data"
StaticPriceSet

I would like to know how to do a for loop were it will
loop through a defined range and add in the references
and then go to the next step.

Thanks


.