Creating a For Loop

J

Justin Ragsdale

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
 
B

Bob Phillips

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)
 
D

Don Guillett

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

Tom Ogilvy

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.
 
J

Justin Ragsdale

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
 
T

Tom Ogilvy

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
 
Top