+ Reply to Thread
Results 1 to 28 of 28

Load a string or array using vba by looping through multiple Sheets

  1. #1
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Load a string or array using vba by looping through multiple Sheets

    Cross posted at :
    https://www.mrexcel.com/board/thread...heets.1171919/

    Reason :
    No solution for some time now.

    Hello guys,
    I have tried to narrow down what I want to achieve to this level and I think with a little push from the seniors on this great platform, my challenge will be handled.



    Please Login or Register  to view this content.
    I want someone to help me out with how to load each of the e into the string or array as explained above?

    Thanks in advance.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,821

    Re: Load a string or array using vba by looping through multiple Sheets

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    From the workbook, I have 5 sheets which reflect the sheet names used in my code above.

    On data 1, 2 is the first missing number from the series
    On data 2, 6 is the first missing number from the series
    On data 3, 1 is the first missing number from the series
    On report 1, 8 is the first missing number from the series
    On report 2, 7 is the first missing number from the series

    So I want to load those numbers to a string variable or an array variable as below.

    ray = Array(2, 6, 1, 8, 7) or
    ray = ("2, 6, 1, 8, 7")
    Attached Files Attached Files

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,846

    Re: Load a string or array using vba by looping through multiple Sheets

    What do you want to do with the resultant array...

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    Kelly mort,
    Try
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Thanks to you geniuses @Sintek and @Jindon

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    Just for your info,

    My code will work with all versions of Excel in terms of generating 1D array.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    9,846

    Re: Load a string or array using vba by looping through multiple Sheets

    Thank you for rep + Kelly...

    And as always...jindon...for your valuable input which we learn from...

  9. #9
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Quote Originally Posted by jindon View Post
    Just for your info,

    My code will work with all versions of Excel in terms of generating 1D array.
    Hello

    In case I want to return the smallest value from the list of numbers that I have generated from your code above, how do I get it with ease?

    Thanks again.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    Do you mean Minimum out of the result?
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Quote Originally Posted by jindon View Post
    Do you mean Minimum out of the result?
    Please Login or Register  to view this content.

    You are a legend! !!!!

    I am learning a lot. Thanks again.

  12. #12
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Can you please explain this line for me?

    Please Login or Register  to view this content.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    That line extracts minimum number that deviated from sequential numbers starting from 1.

    row(1:" & LR - 2 & ") creates array of {1,2,3,4,5,6, up to LR-2}
    -2 is just for adjustment of starting row that is 4(A4) and it is not necessary in this case.

    isna(match(row(1:" & LR - 2 & "),a4:a" & LR & ",0))
    find if any of the number in array is/are missing in A4:A LR

    You can actually view how it works on the sheet.
    Select D4:D10 in "Data 1" sheet and enter formula
    =ISNA(MATCH({1;2;3;4;5;6;7;8;9;10;11},A4:A10,0))
    and confirm with Ctrl + Shift + Enter (array formula entry)

    Now you see
    FALSE
    TRUE
    FALSE
    FALSE
    FALSE
    TRUE
    FALSE

    So, the minimum missing number is 2nd row then Min returns 2 from 2nd row(1:" & LR - 2 & ").

    HTH

  14. #14
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Okay thanks for the explanation.

    I just observed something that I want you to look at it for me so that I don't enter into some serious trouble later:

    I am running some test with Data 1 and Data 2 sheets.

    Numbers on Data 1 are in this order:
    Please Login or Register  to view this content.
    And when I run the code with all other sheets being blank, it returns 5 which is correct.

    Then I added some data to Data 2 sheet as below:
    Please Login or Register  to view this content.
    This time, the code gives me 5 instead of 6.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    It should give you 6 (tested here).

    If not on your side, if you upload a workbook that is giving 5, I will look at it.

  16. #16
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    With the data that I provided, the code should give 5 as the min value and your code is correct. The issue is from my end - the logic that I gave (just finding the smallest value) is not enough!

    Data1 has this:
    3
    2
    1
    10
    7
    4

    And this part will return 5 as the min value.

    Then Data 2 has this:
    1
    2
    3
    4
    5

    And this part will return 6 as the min value.

    Now we have the list as (5,6).
    So when we run the min code against the list, we surely return 5.

    The actual number that I want to return (defined previously as min value from the list of missing numbers), is a number that should not be present on any of the sheets.

    I never anticipated this kind of trap when I was thinking it through. I was thinking getting the missing numbers then getting the smallest out of the list could save me.

    But I just realized that the smallest from the list could also be found on other sheets.

    So the goal in a simpler form, is to locate a missing number, that does not exist on any of the sheets.

    Can that be done?

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    Then it should return 6 for those 2 data sets?

    OK, now I understand what you are trying to do.
    I need to go out now, so I will post a code later like 3-4 hours.

  18. #18
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    OK. I will be waiting then.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    See if this is how you wanted.
    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    The code is doing well and producing the right output.
    But when one or more sheets have only one row filled, say Data 1 has only one record, the code runs into a mismatch error on this line:
    Please Login or Register  to view this content.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Quote Originally Posted by jindon View Post
    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Problem solved.

    Thanks again for your time.

    Have a wonderful time.

  23. #23
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Quote Originally Posted by jindon View Post
    See if this is how you wanted.
    Please Login or Register  to view this content.
    Hello Jindon,

    Sorry for waking up this thread again but I need one more help.

    In case I want to point to column M on the report 2 sheet, how do I adjust the code to do that for me?

    That is for all the other sheets, I am pointing to column A for the numbers but when I get to report 2, I want to look at column M for the numbers.

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    Do you mean like this?
    Please Login or Register  to view this content.
    Last edited by jindon; 07-22-2021 at 09:09 PM.

  25. #25
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,872

    Re: Load a string or array using vba by looping through multiple Sheets

    Just spotted a typo.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  26. #26
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    @Jindon,
    That's correct, from the question I asked above.

    The real question should have been, pointing to both columns A and M on the Data * sheets.

    So for data 1 to data 3, I want to point to both A and M but on the report sheets, I point to just column A.

    I was a bit tired and could not concentrate properly while asking the question and I hope you could forgive me for that.

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,947

    Re: Load a string or array using vba by looping through multiple Sheets

    Then try
    Please Login or Register  to view this content.

  28. #28
    Registered User
    Join Date
    09-23-2019
    Location
    Ghana
    MS-Off Ver
    2016 32 bit and 64 bit
    Posts
    82

    Re: Load a string or array using vba by looping through multiple Sheets

    Thanks Jindon for it.

    This is exactly what I needed.

    Have a wonderful weekend.

+ 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. Load worksheet to array, load new worksheet with specific parts of array
    By garden_gnome in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-08-2019, 04:47 PM
  2. Array that selects multiple sheets based on cell string
    By Andy Swain in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-03-2018, 07:20 AM
  3. Select multiple sheets(array) if sheet names are from cell value or string
    By marvay in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2018, 11:26 PM
  4. Load multiple elements of an array at once
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2017, 09:37 PM
  5. [SOLVED] Looping an Array Formula for Multiple rows
    By captainangela in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-08-2014, 12:08 PM
  6. [SOLVED] Multiple Sheets be reflected in 1 sheets as a summary (looping)
    By Kooper in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2012, 03:21 AM
  7. Looping An Operation Through Multiple Worksheets Using An Array
    By Authentik8 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-12-2012, 10:04 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