+ Reply to Thread
Results 1 to 11 of 11

How to loop through VBA defined variables

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    How to loop through VBA defined variables

    Hi,

    I have defined a set of variables 'mydata1', 'mydata2' ... 'mydata35' using the value of a named cell in a closed sheet

    I would then like to loop through these variables to complete a repetative bit of code, think it should be possible, but im not sure how?

    My code is below, any questions then just ask, thanks.

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to loop through VBA defined variables

    You cant reference it like that, you can put them in an array MyData(j) will then work, that will give you a blank variable concatenated with J, so just a number.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to loop through VBA defined variables

    Yeah, I didnt think so, was just to illustrate.

    How would I go about using an array in this example, I've not had any experience using them (been meaning to learn

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to loop through VBA defined variables

    Google them, pretty much what you have, but it would be MyData(1)="Nathan", myData(2)="Is Brill"

  5. #5
    Registered User
    Join Date
    08-01-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to loop through VBA defined variables

    I have successfully defined an array and populated it, however Im getting an error when trying to call the value from the array.

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to loop through VBA defined variables

    Is the formula right?

  7. #7
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: How to loop through VBA defined variables

    Hi..

    What are you ultimately trying to do..?

    If you post some more details.. (a sample Workbook would be good). perhaps we can help you make it more efficient..

  8. #8
    Registered User
    Join Date
    08-01-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to loop through VBA defined variables

    Apo: The objective of the program is to pull information from named cells in many similar workbooks and place it on a worksheet, without having to open the workbooks and copy/paste the info.

    nathansav: You were right to question the formula, my code now works.

    For some reason when a filepath contains an appostrophe - ' - then it has to be replaced with two apostrophes - '' [NOT "] - when using the file calling technique employed here. e.g. HMO's becomes HMO''s. Does anyone know the reason for this out of interest??

    Note: I am calling the files from a mapped network drive, not a local one on the computer.

    Also I had to remove the square brackets and didnt need to include the sheet name. For reference my new code is below:

    Please Login or Register  to view this content.

  9. #9
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: How to loop through VBA defined variables

    Hi..

    Just out of interest.. do you have 34 more lines similar to this:

    Please Login or Register  to view this content.
    I mean.. are you populating the MyData array via 35 lines of code or using a loop etc?

  10. #10
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to loop through VBA defined variables

    doubling them up stops their original purpose, so ""nathan"" will show as "nathan" but the extra " is used so its still a string.

    so your 's are used in file links etc

  11. #11
    Registered User
    Join Date
    08-01-2012
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to loop through VBA defined variables

    apo: Yes there are another 34 lines of similar code. Each element in the MyData array is populated with the value of a certain cell name. These cells sometimes change position in the different workbooks being read and are not in a list (hence they are named for reference) so I cant think of an alternative to cycle through and populate the array with a loop. Any Ideas.

    nathansav: Ah ok, makes sense now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Delete all user-defined variables
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-26-2013, 02:04 PM
  2. [SOLVED] Use variables defined in sub in my function
    By niko79542 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2013, 11:57 AM
  3. Sum between two defined variables (1st row - last row)
    By uncleslinky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-15-2011, 06:34 AM
  4. Name and Save Document Using Defined Variables
    By odo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-29-2009, 02:46 PM
  5. Draw a curve with defined variables
    By UVKutt in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-30-2008, 05:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1