• Status: Solved
• Priority: Medium
• Security: Public
• Views: 182

# Paste Data

Hello trying to build a vba code

Sheet3 range A1 is initial criteria
Search for this on or the closest value in Sheet1 range CB2:DD3
If value found, look at column values in CA of the row found in, use this as the second criteria
Search Sheet3 in range W2:A2for the second criteria

Then paste all the values in the respective columns.  In Sheet 3 I put filled in a couple of the columns to show how far down i need it.  Thanks.
Data1.xlsm
0
sandramac
• 8
• 7
1 Solution

Commented:
Let's try. You wrote:-
Search for this on or the closest value in Sheet1 range CB2:DD3
Rows 2 and 3 contain very different kinds of data. Do you mean CB2:DD2?
Please specify the "closest": Presuming that 17 isn't available, and that both 16 and 18 are available, which is the "closest"?
NB. Excel would specify this as <= or >=
Further, presuming that there are several "closest" would you want the furthest to the left or the furthest to the right?

You wrote:
If value found
VBA would like to know what if it isn't found. In fact, is it possible that it isn't found? Blank cells would have a value of zero and zero could be the closest to any other number. Do you need to differentiate between zero and nothing?

You wrote:
look at column values in CA of the row found in, use this as the second criteria
The first look-up returned a column. The row of that column is row 2. Therefore the return value would always be CA2, right?

You wrote:-
Search Sheet3 in range W2:A2 for the second criteria
This range is blank in your workbook.

You wrote:-
Then paste all the values in the respective columns
Now, that is a tall order! Which values? To which columns? On which sheet?

0

Author Commented:
Faustulus:  Sorry let me try to be more precise.

When I run a vba code i need it to perform the following steps

1. Use the value on Sheet 3, cell A1 and find value on sheet 1 CB2:DD2.  There could be multiple values, so just need to find the first occurrence which will be the starting point.  If the value is not found find the closest value, to the right of the referenced value.  So in the example the value is 17, so we would use the column with 18. This is the matched column.

2.  Now match each criteria in Sheet 3 W2 to AE2 to Sheet 1 CA3 to CA22. and then take that matched value and paste into sheet 3, in the area of W3:AE3.  For example. Sheet 3 W2 it is Mean SLP (mb), so find that in sheet1 CA2:CA22.  Which in this case is cell CA4.  You now have the matched row.  Now get the value from the matched row and matched column, which in this case would be 1013.3.  From this cell copy all the data from that point all the way across DD4 and paste in sheet 3, down the column.  I need this accomplished for each header in sheet 3 W2:AE2.
0

Commented:
I'll look at this on my PC a little later. Meanwhile, if 17 is desired and 18 doesn't exist, would the closest value be 19 or 16?
Is there a conceivable condition when no match can be made - either due to irregular or missing data - and what should be the progam's response to that?
0

Commented:
I'll look at this on my PC a little later. Meanwhile, if 17 is desired and 18 doesn't exist, would the closest value be 19 or 16?
Is there a conceivable condition when no match can be made - either due to irregular or missing data - and what should be the progam's response to that?
0

Author Commented:
19 would be used. the 17 will not always be constant, the number will change based on the user.  Basically whatever the number is I need to match the exact number or the next number above that.
0

Commented:
That means, it is possible that there is no match. What should the program do?
You also avent answered my question about which column to select if there is more than one match.
0

Author Commented:
Yes, there is a possiblity that there will be no exact match, so use the next higher number so if sheet 3 cell a1 is 17, and there is no 17, look for 18, then 19, then 20.  I need the column of the first matched column.
0

Commented:
What if the number you are looking for is higher than any number in the list? Say, you are looking for 17 and the highest number in the list is 16.
Say, you are looking for 17 and there are several 17's in the list. Will it be OK to use the column of the first match? First match from the left or from the right - meaning, oldest or newest?
0

Author Commented:
Did not think of that, thanks for pointing that out.  I would need to find the next lowest number in that case so if nothing higher than 17, use 16 or 15.   If multiple 17's, then use the first match. First look to the newest, then if not found go to the oldest
0

Commented:
sandramac,
My result isn't anywhere near what you have predicted but it is a result. So we can take it from there. I think I have implemented all your instructions carefully (E. & O.E.)
To install the code just drag the module ExtractMan to your project. To run it, run the procedure ExtractData. If you need to run it from the keyboard or the macro list remove the word "Private" in the declaration line of that procedure only.
In the ExtractData procedure you can set the constant CrierionSource which is a cell reference on Sheet2. You can rename the worksheets.
Above that procedure you have two enums which specify the ranges, rows and columns. You can modify all of those (within reason).
EXX-130622-Multiple-Lookups.xlsm
0

Author Commented:
Faustulus,

I ran your example file.  It gave me the starting data of of cell CX4, however it should be cell CH4, that is the first number in the column that is closest to 17.   Thank you for all the work and help
0

Commented:
If multiple 17's, then use the first match. First look to the newest, then if not found go to the oldest
I presume that the newest would be the furthest to the right. Therefore my program starts looking from the right and finds CX as the first occurrence of the nearest match.
Do you need this system changed? It's easy.
0

Author Commented:
Yes, if it could start from the left.
0

Commented:
In the function 'TestColumn' please look for this line of code:
For C = Rng.Cells.Count To 1 Step -1
replace it with the following.
``````For C = 1 To Rng.Cells.Count
``````
0

Author Commented:
Thanks for all the work, it is working
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.