PDA

View Full Version : Solved: Lookup values in varying locations?



pingwin77
07-21-2009, 02:57 PM
I have a worksheet that generates tables of different sizes. I need to get information out of them and into another sheet.

I attached a sample of what I am working with. The values I need are always going to be location in Column "F." The difference will be 2 fold.

1. the number of values in each grid of data (dimensions)

2. the number of grids in the sheet. (parts)

these 2 variable will continuously change the location of the data in column "F"

I added color to show what cells need to be grouped together. Sheet 1 has 3 parts and 6 dimensions and sheet 2 has 4 parts and 5 dimensions. These are just examples. There could be many more of both parts and samples.

I would like to have all the cells that represent Dim 1 on Sheet 1 (orange) be collected on Sheet 3 in Column A. All the dims that represent Dim 2 on Sheet 1 (yellow) be collected on Sheet 3 Column B and so on.

I'm sure this is confusing so please ask any questions. thanks!

nst1107
07-21-2009, 06:40 PM
My suggestion is this: you will know how many parts and dimensions you are dealing with when you go to seperate and copy the data (you are already using variables to get and store this information from the form) a = TextBoxPARTS
b = TextBoxDIMSFrom that you should know what the spacing is between the cells of interest (looks like it is always going to be the number of dimensions plus 5) and how many cells there are. I would dim 2 more variables (or just use a couple of the many you've already dimmed), one to loop through the number of parts and one to loop through the number of dimensions, and systematically copy the cells over to Sheet 3. Something like this: For Counter = 1 To b '<-- Loop through the dimensions
For numRows = 1 To a '<-- Loop through the parts
Sheet3.Cells(numRows, Counter) = Sheet1.Cells(7 + (5 + b) * (numRows - 1), "F")
Next
Next

pingwin77
07-22-2009, 06:25 AM
I think I understand what that is doing. I failed to mention in the first post that this will be executed with a second button once the form has been created then filled out. How would I set this up for a second button click?

I need to have variable "a" and "b" defined. Is there a way to do that globally so I can access then in any other code I write? If not, how do I pull a value out of the cells in my report and store those as variables?

Thanks for all the help and patience on this as I am just starting to learn how all this VBA stuff works.

nst1107
07-22-2009, 07:34 AM
I see now that you have a couple of cells on the sheet that say what your parts and dims are. You can the two "b"s in my code with "Sheet1.Cells(2,"D")" and replace the "a" with "Sheet1.Cells(2,"C")". This will be cleaner for that small amount of code than assigning the cell values to variables first.

If you want to make a and b "global" variables, move their Dim statements to above all the procedures in your module, just below Option Explicit, and make sure you remove their respective dim statements within your other procedures.

pingwin77
07-22-2009, 07:38 AM
I will give that a shot, thanks!

pingwin77
07-22-2009, 07:47 AM
So I gave that code a try and the counting seems to work. I get the first DIM worth of data in the correct order and the for the DIM 2, it move to column B but still has the data from DIM 1. So the locations are correct but it keeps pulling the same data each time it move to the next column.

I was also wondering ho to have the data on sheet 3 start in row 5 or 6 and how to adjust that row value in case i need some more room at the top of the sheet?

pingwin77
07-22-2009, 07:50 AM
here is the file so far with some sample data in it.

nst1107
07-22-2009, 11:32 AM
My mistake. I'm sorry. Need to make this adjustment:Sheet3.Cells(numRows, Counter) = Sheet1.Cells(7 + (Counter - 1) + (5 + f) * (numRows - 1), "F")To change where it starts putting data in Sheet3, add a number or variable to numRows, such as:Sheet3.Cells(numRows + urNumber, Counter) = etc...

pingwin77
07-23-2009, 09:04 AM
I made the changes and that fixed it! Thanks again for all the help!