Solved

Paste Data

Posted on 2013-06-20
15
170 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 500 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now