vba - I'd like to run a loop, but change 2 variables each instance -
i trying edit vba code. customized needs, repeating same process on 20 times, , have entered code 20 times , changed variables.
i have 100 instances, , don't want manually this. how can edit code runs 100 times while changing variables?
as see, have included same code twice slight changes. need change ticker , range each iteration, , also, destination range need move 1 column on each time.
by way, pulling yahoo finance stock info.
sub data_get() ' ' data_get macro ' dim ticker1, ticker2, ticker3 string, sday, smonth, syear, eday, emonth, eyear long ticker1 = range("l1") ticker2 = range("l2") ticker3 = range("l3") sday = day(range("l4")) smonth = month(range("l4")) - 1 syear = year(range("l4")) eday = day(range("l5")) emonth = month(range("l5")) - 1 eyear = year(range("l5")) ' activesheet.querytables.add(connection:= _ "text;http://real-chart.finance.yahoo.com/table.csv?s=" & ticker1 & "&d=" & emonth & "&e=" & eday & "&f=" & eyear & "&g=d&a=" & smonth & "&b=" & sday & "&c=" & syear & "&ignore=.csv" _ , destination:=range("$a$1")) .name = "data" .fieldnames = true .rownumbers = false .filladjacentformulas = false .preserveformatting = true .refreshonfileopen = false .refreshstyle = xlinsertdeletecells .savepassword = false .savedata = true .adjustcolumnwidth = true .refreshperiod = 0 .textfilepromptonrefresh = false .textfileplatform = 437 .textfilestartrow = 1 .textfileparsetype = xldelimited .textfiletextqualifier = xltextqualifierdoublequote .textfileconsecutivedelimiter = true .textfiletabdelimiter = true .textfilesemicolondelimiter = false .textfilecommadelimiter = true .textfilespacedelimiter = false .textfilecolumndatatypes = array(5, 9, 9, 9, 9, 9, 1) .textfiletrailingminusnumbers = true .refresh backgroundquery:=false end activesheet.querytables.add(connection:= _ "text;http://real-chart.finance.yahoo.com/table.csv?s=" & ticker2 & "&d=" & emonth & "&e=" & eday & "&f=" & eyear & "&g=d&a=" & smonth & "&b=" & sday & "&c=" & syear & "&ignore=.csv" _ , destination:=range("$c$1")) .name = "data2" .fieldnames = true .rownumbers = false .filladjacentformulas = false .preserveformatting = true .refreshonfileopen = false .refreshstyle = xlinsertdeletecells .savepassword = false .savedata = true .adjustcolumnwidth = true .refreshperiod = 0 .textfilepromptonrefresh = false .textfileplatform = 437 .textfilestartrow = 1 .textfileparsetype = xldelimited .textfiletextqualifier = xltextqualifierdoublequote .textfileconsecutivedelimiter = true .textfiletabdelimiter = true .textfilesemicolondelimiter = false .textfilecommadelimiter = true .textfilespacedelimiter = false .textfilecolumndatatypes = array(9, 9, 9, 9, 9, 9, 1) .textfiletrailingminusnumbers = true .refresh backgroundquery:=false end end sub
do following ...
dim long, niter long ' calculate number iterations here, using calcuation niter = 100 ' same job repeatedly iloop = 1 niter ' put code want repeat in here ' if want move down in range every iteration, 1 possible way follows... activesheet.querytables.add(connection:= _ "text;http://real-chart.finance.yahoo.com/table.csv?s=" & ticker1 & _ "&d=" & emonth & "&e=" & eday & "&f=" & eyear & "&g=d&a=" & smonth & _ "&b=" & sday & "&c=" & syear & "&ignore=.csv" _ , destination:=range("$a$1").offset(iloop-1,0)) next iloop
Comments
Post a Comment