Collecting data with a dictionary vba

[drop05]

Hello, I was told to collect before worrying about printing and doing so using a dictionary and a unique key
The unique key is something in the outer dictionary is what i was told too

But i am not sure what that means, or the creation of a dictionary.
i am new to VBA and trying to create something that does this to assist with copying and pasting values from one sheet to another

any tips or explanations to kinda get an idea of starting

 

[Eric W]

That's a huge subject! Here's a link explaining how to define and use dictionaries:

VBA for smarties: Dictionaries

www.snb-vba.eu

As far as copying/pasting values from one sheet to another, that's too vague to really give any advice about. Use the search feature of this forum to look for "Scripting.Dictionary" and you'll see a wealth of examples of people using dictionaries for various purposes. Work through some of the shorter examples to see how the dictionary is used. If you have a specific question, come back and let us know.

 

[drop05]

That's a huge subject! Here's a link explaining how to define and use dictionaries:

VBA for smarties: Dictionaries

www.snb-vba.eu

As far as copying/pasting values from one sheet to another, that's too vague to really give any advice about. Use the search feature of this forum to look for "Scripting.Dictionary" and you'll see a wealth of examples of people using dictionaries for various purposes. Work through some of the shorter examples to see how the dictionary is used. If you have a specific question, come back and let us know.

Thank you i will check it out.

Yeah what i am trying to do is get data from one place to another but was told using a dictionary and unique key but know nothing about it so trying to see a way to start
I have ranges of descriptions and have their own values, ill show two below. The values that are entered by a user start on G7 and then go all the way down to G356 where the last description is listed, on C column but value would go in the G column. Then same chunk of descriptions and all starts again at row 358 where the user can enter values again so, each test has 350 cells to enter values and if you include the row where its a header than 351, Is there a way to use that range in a loop, collect the data from each test which has the same range within each one as described, continue the loop until the last value in Column G then move on to H, do the same with collecting the data until last value with the loop and so on to I, J?

 

[Eric W]

It depends on what you're going to do with the data once you have it. For example, you can use this:

MyData = Range("G8:J356").Value

to read that whole range into an array you can reference in your macro. And it would be faster than using a dictionary. What are you going to do with it though? Sort it, analyze it, copy to another sheet? These are the questions you need to ask when deciding what kind of data structure to use.

 

[drop05]

It depends on what you're going to do with the data once you have it. For example, you can use this:

MyData = Range("G8:J356").Value

to read that whole range into an array you can reference in your macro. And it would be faster than using a dictionary. What are you going to do with it though? Sort it, analyze it, copy to another sheet? These are the questions you need to ask when deciding what kind of data structure to use.

Yes, i would like to copy it to another sheet.
However from the first screen shot you can see the numbers 1,2,3 etc in columns G starting and can keep going beyond J. Also G will have multiple tests, H will have multiple tests and so on. That is why id like to loop through each test for G until there is no more values then go to H and do the same and so on. Since i was told collecting the data then worrying about printing the data. So i guess collecting the data like that and then pasting into "Sheet2" for now.
If its possible when pasting the data that is collected for each test it will go into its own row So say G has two tests with values then the data collected from the first test from G goes into sheet2 column A, then test 2 from G goes into B. There are no more values left to collect from G from sheet 1 so goes to H and starts that loop again and say H has three tests of values so then Test 1 for H data values will go into sheet2 column C since A-B will have values. Then test 2 from column H data will go to D since now A-C has values, and the third test from H goes into column E since now A-D has values.
Is that something that can be done?
Also Copy that, was Just was told using a dictionary would be easier but if there is another way that would be helpful to know!

 

[drop05]

It depends on what you're going to do with the data once you have it. For example, you can use this:

MyData = Range("G8:J356").Value

to read that whole range into an array you can reference in your macro. And it would be faster than using a dictionary. What are you going to do with it though? Sort it, analyze it, copy to another sheet? These are the questions you need to ask when deciding what kind of data structure to use.

Does that explanation help answer the questions in regards to what i want to do with the data?
Is this something to code out in VBA? As i said i am new to the language and would appreciate any help!

 

[Eric W]

How many tests are you talking? Dozens or hundreds? (There's a simple but slow way if there's only dozens. But if more, it's best to code for performance.) Are the headings/descriptions repeated down the sheet multiple times? Will every cell in a range be filled? How can you tell how many tests are run per column? Just look for the last filled cell, and assume that everything above has been used? When copying, do you just want the entire 350-cell range moved, with no manipulation of the data?

 

[drop05]

How many tests are you talking? Dozens or hundreds? (There's a simple but slow way if there's only dozens. But if more, it's best to code for performance.) Are the headings/descriptions repeated down the sheet multiple times? Will every cell in a range be filled? How can you tell how many tests are run per column? Just look for the last filled cell, and assume that everything above has been used? When copying, do you just want the entire 350-cell range moved, with no manipulation of the data?

the shell goes down to test 20, so G could have up to 20, H could have up to 20 and so on. So how the screen shot shows "Test - 1" it goes all the way to "Test - 20", with the last row being 7025. Same descriptions with headers for each test, only thing that changes is the word Test - 1 to Test - 2 and so on. So each column can have up to 20 tests since test 20 is the last one but does not mean that test 20 will have data so kinda like last cell that is filled then jump to column H, last field that is filled. So G can have test 1-10 filled and then stops at 10 so after getting the last data cell then go to H and say it has 5 then get the data and move on to the next column.
But yeah essentially yeah last cell filled and assume the above is filled because it should not do say test 1, 2, skip 3, do 4, it should not be that way, should be consistent when data is entered. And data should not be changed
And each test has that range, so at first i was thinking of a
for i to #
kinda situation but may be wrong

im hoping i am answering your questions in regards to my question for this code.

 

[drop05]

How many tests are you talking? Dozens or hundreds? (There's a simple but slow way if there's only dozens. But if more, it's best to code for performance.) Are the headings/descriptions repeated down the sheet multiple times? Will every cell in a range be filled? How can you tell how many tests are run per column? Just look for the last filled cell, and assume that everything above has been used? When copying, do you just want the entire 350-cell range moved, with no manipulation of the data?

curious on which way youd suggest and if you do not mind showing the way, id love to see and learn the process.
Also would like to say thank you in advance as well!

 

[drop05]

Bump