0

Shortened the post. Complete code in the attached excel file.

Need help with correcting the looping code so that Variable gets value from a cell of another sheet repeats after certain process.

What's working correct already:: For a Single run::

  1. Currently, works only on 1 input (saved in ParameterSheet of workbook)
  2. Downloads data from gfinance
  3. Modifies data in consumable format (also adjusting time values)
  4. Exporting as csv
  5. Importing into another application

(Here's the excel file for reference)

Modifying the macro to work in a loop on a list of symbols in SymbolSheet. This list and count of list-items can keep changing.

Below is the code i have tried unsuccessfully :

'Starting Symbol loop --- Will DO STUFF till there are symbols in Symbol sheet

kal = SymbolSheet.Range("A1048576").End(xlUp).Row

For io = 1 To kal

ticker = Cells(io, 1).Value   ' fill ticker with cell value one by one

Symbolll = ticker & "-EQ"


'DO LOTS OF OTHER WORK

'Below code is coming from top where Ticker value is set

 Next io`
Community
  • 1
  • 1
Vaibhav
  • 123
  • 8
  • Limit the code to the bare minimum required to replicate your problem. The solution is likely quite simple but it's hard to dig through at the moment. – Grade 'Eh' Bacon Oct 14 '15 at 17:45
  • Sorry I don't see the problem - this looks like it should loop through all of 1 to kal. What is the error that is currently happening? What value did you expect and what did you get? – Grade 'Eh' Bacon Oct 14 '15 at 17:57
  • 1
    if you qualify the sheet in this line `ticker = Cells(io, 1).Value` it may help. For example, `ticker = Sheets("Sheet1").Cells.(io,1).Value` *where Sheet1 is the needed sheet name* If the active sheet is any other than where you desire to get the ticker from, they way you have it written will not work. – Scott Holtzman Oct 14 '15 at 17:57
  • Excel vba throwing syntax error around the starting bracket of --> `ticker = Sheets("Symbol").Cells.(io,1).Value` Also, same syntax error when modified like: `Set SymbolSheet = Sheets("Symbol") ticker = SymbolSheet.Cells.(io,1).Value ` Am i doing something wrong here ? – Vaibhav Oct 14 '15 at 18:08
  • 2
    Take out `.` after `cells`? – findwindow Oct 14 '15 at 18:14
  • Thanks, i did that. VBA creates a folder and closes off. Does nothing else at all ! Not sure if it is executing the loop. Going to use debug and message box (if that is possible) to get value of the symbol read. If you have any suggestions pls do share. – Vaibhav Oct 14 '15 at 18:23
  • Thanks, the errors have solved and the looping is working fine. Some other error is cropped up into data. Which now needs to be looked into. Thank you both for helping and giving your time. – Vaibhav Oct 14 '15 at 18:44

1 Answers1

0

The answer has been provided by Scott. Given below:

if you qualify the sheet in this line ticker = Cells(io, 1).Value it may help. For example, ticker = Sheets("Sheet1").Cells(io,1).Value where Sheet1 is the needed sheet name If the active sheet is any other than where you desire to get the ticker from, they way you have it written will not work. – Scott Holtzman

Vaibhav
  • 123
  • 8