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

[Kelly mort]

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.

[TMS]

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.

[Kelly mort]

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")

[sintek]

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

Please Login or Register  to view this content.

[jindon]

Kelly mort,
Try

Please Login or Register  to view this content.

[Kelly mort]

Thanks to you geniuses @Sintek and @Jindon

[jindon]

Just for your info,

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

[sintek]

Thank you for rep + Kelly...

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

[Kelly mort]

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.

[jindon]

Do you mean Minimum out of the result?

Please Login or Register  to view this content.

[Kelly mort]

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.

[Kelly mort]

Can you please explain this line for me?

Please Login or Register  to view this content.

[jindon]

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

[Kelly mort]

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.

[jindon]

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.

[Kelly mort]

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?

[jindon]

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.

[Kelly mort]

OK. I will be waiting then.

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[Kelly mort]

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.

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Kelly mort]

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.

[Kelly mort]

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.

[jindon]

Do you mean like this?

Please Login or Register  to view this content.

[bakerman2]

Just spotted a typo.

Please Login or Register  to view this content.

[Kelly mort]

@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.

[jindon]

Then try

Please Login or Register  to view this content.

[Kelly mort]

Thanks Jindon for it.

This is exactly what I needed.

Have a wonderful weekend.