Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Paste Data

Posted on 2013-06-20
15
Medium Priority
?
178 Views
Last Modified: 2013-07-10
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
Comment
Question by:sandramac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39264926
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?

NB. I think the scope of this thread could be to help you specify your requirements. Actually programming the code will be another lengthy task.
0
 

Author Comment

by:sandramac
ID: 39264951
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39265017
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Faustulus
ID: 39265018
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 Comment

by:sandramac
ID: 39265030
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39265043
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 Comment

by:sandramac
ID: 39265903
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39266208
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 Comment

by:sandramac
ID: 39266228
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
 
LVL 14

Accepted Solution

by:
Faustulus earned 2000 total points
ID: 39267645
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 Comment

by:sandramac
ID: 39268746
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39268906
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 Comment

by:sandramac
ID: 39268909
Yes, if it could start from the left.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39268942
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

Open in new window

0
 

Author Closing Comment

by:sandramac
ID: 39316123
Thanks for all the work, it is working
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question