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

Popular posts from this blog

javascript - How to get current YouTube IDs via iMacros? -

-
i want viewed youtube video id , save csv via imacros, gets nothing. here js code: var videoid; videoid ="code:"; videoid +="url goto=https://www.youtube.com/watch?v=dbnywxdz_pa"+"\n"; videoid +="add !extract {{!urlcurrent}}"+"\n"; videoid +="set !var1 eval(\"var s=\"{{!urlcurrent}}\"; s.match((?<=watch\\?v=|/videos/|embed\\/)[^#\\&\\?]*);s[0]; \")"+"\n"; videoid +="add !extract {{!var1}}"+"\n"; videoid +="saveas type=extract folder=* file=url.csv"+"\n"; try this: var videoid = "url goto=https://www.youtube.com/watch?v=dbnywxdz_pa" + "\n"; videoid += "set !extract {{!urlcurrent}}" + "\n"; videoid += 'set !extract eval("\'{{!extract}}\'.match(/v=(.{11})/)[1];")' + "\n"; videoid += "saveas type=extract folder=* file=url.csv" + "\n"; iimplay
Read more

c# - Maintaining a program folder in program files out of date? -

-
for program i'm writing in vs c# wpf need store user related information on user's computer. far knew has been done creating folder in c:\program files , adding whatever program related info folder in subfolders or whatever. after doing browsing came across lot of people saying method out of date because access may denied create folder there, works administrative accounts, etc.. 1 site suggested saving c:\users\username\appdata\roaming or c:\users\username\appdata\local. question best , date method saving program data users computer? also take @ msdn article . it uwp apps have general idea put if developing wpf app. you may need these folders: environment.specialfolder.applicationdata environment.specialfolder.localapplicationdata environment.specialfolder.commonapplicationdata
Read more

emulation - Android map show my location didn't work -

-
i have been struggle current location in google map, tried official map demo, not working also. shows getmylocation button on upper right, didn't show blue dot on map current location. here code , i'm using emulator target api 23, guess may emulator's problem since code download directly github. advice appreciated. [screenshot][1] package com.example.mapdemo; import com.google.android.gms.maps.googlemap; import com.google.android.gms.maps.googlemap.onmylocationbuttonclicklistener; import com.google.android.gms.maps.onmapreadycallback; import com.google.android.gms.maps.supportmapfragment; import android.manifest; import android.content.pm.packagemanager; import android.os.bundle; import android.support.annotation.nonnull; import android.support.v4.app.activitycompat; import android.support.v4.content.contextcompat; import android.support.v7.app.appcompatactivity; import android.widget.toast; /** * demo
Read more