Solved

Paste Data

Posted on 2013-06-19
9
269 Views
Last Modified: 2013-06-20
Hi,

Looking for a vba code to search for the value in sheet 3 A1, in sheet 1 BE2 to BW 6.  When found, then search for criteria in Sheet 3 K2:T2 in Sheet 1 BD3:BW 23.  When both criteria are found copy and paste the data into sheet 3 K3.  I have attached an example of what the data is suppose to look like.  The valube in sheet 3 A1, is not constant it will change upon certain conditions.
Data.xlsx
0
Comment
Question by:sandramac
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39261247
Hello there,

Your criteria isn't clear.  For example, you say if the value was found on Sheet1 in BE2:BW6, then search for "criteria" in Sheet3!K2:T2.  Is "criteria" the same value in Sheet3!A1?  In your sample file it is "17".  Also, in Sheet3!K2:T2 are column headers.  Is that right?  If it's column headers, what should they be matched against?

It's all very confusing.  I don't know if these are right, but maybe a more detailed explanation like this....

For example:
Sheet3 range A1 is initial criteria
Search for this on Sheet1 range BE2:BW6
If value found, look at column values in BD of the row found in, use this as the second criteria
Search Sheet3 in range K2:T2 for the second criteria

Then in your attached file, you could point out where the value is found and what should be returned.  Highlighting cells works really well.  :)

HTH
Regards,
Zack Barresse
0
 
LVL 1

Expert Comment

by:noExpert
ID: 39262109
I think this is what he wants.
Look for the value in A1 to choose a column and then use the headings fromK2 to T2 as criteria to pick rows. Then take the corresponding values and insert these in row 3 under the headings on sheet3.
If so the code below should suffice.


Sub SelectAndInsertValues()
    Dim rf, rf2 As Range
    Dim results(11) As String
   

    criteriaY = Sheets("Sheet3").Cells(1, 1)
    Sheets("Sheet1").Activate
    Range("BD2:BW2").Select
   
    Set rf1 = Selection.Find(What:=criteriaY, After:=ActiveCell)
    If Not rf1 Is Nothing Then
       
        For counterX = 11 To 20
            criteriaX = Sheets("Sheet3").Cells(2, counterX)
           
            Range("BD2:BD23").Select
            Set rf2 = Selection.Find(What:=criteriaX, After:=ActiveCell)
            If Not rf2 Is Nothing Then
                results(counterX - 10) = Cells(rf2.Row, rf1.Column)
            End If

        Next counterX
       
        Sheets("Sheet3").Activate
        Range("K3:T3").Select
        Selection.Insert Shift:=xlDown
       
        For counter = 11 To 20

            Cells(3, counter) = results(counter - 10)
        Next counter
    End If

End Sub

Alan
0
 

Author Comment

by:sandramac
ID: 39262499
noExpert, you are correct that is what I am needing.  I tried running the code, but it is only giving me the first line of data.  i attached an updated excel with your code.
Data1.xlsm
0
 

Author Comment

by:sandramac
ID: 39262515
firefytr, sorry for the confusion
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Expert Comment

by:noExpert
ID: 39262992
I thought you just wanted the selected record put to the top.
You actually want the selected record plus all records following listed.
This does that:

Sub SelectAndInsertValues()
    Application.ScreenUpdating = False
   
    Dim rf, rf2 As Range
    Dim results(11) As String
   
    endCol = 75
    rowFill = 3
    criteriaY = Sheets("Sheet3").Cells(1, 1)
    Sheets("Sheet1").Activate
    Range("BD2:BW2").Select
   
    Set rf1 = Selection.Find(What:=criteriaY, After:=ActiveCell)
    If Not rf1 Is Nothing Then
        startCol = rf1.Column
        For counterRowsToFill = startCol To endCol
       
            For counterX = 11 To 20
                criteriaX = Sheets("Sheet3").Cells(2, counterX)
               
                Range("BD2:BD23").Select
                Set rf2 = Selection.Find(What:=criteriaX, After:=ActiveCell)
                If Not rf2 Is Nothing Then
                    results(counterX - 10) = Cells(rf2.Row, startCol)
                End If
   
            Next counterX
           
            Sheets("Sheet3").Activate
           
            For counter = 11 To 20
                Cells(rowFill, counter) = results(counter - 10)
            Next counter
           
            rowFill = rowFill + 1
            startCol = startCol + 1
            Sheets("Sheet1").Activate
        Next counterRowsToFill
    End If
    Sheets("Sheet3").Activate
    Application.ScreenUpdating = True

End Sub


Al
0
 
LVL 1

Accepted Solution

by:
noExpert earned 500 total points
ID: 39263126
Apologies but my code above would pick out 15 if 5 were entered as it was looking at any part of the cell in the find method. Its corrected below.

Al

Sub SelectAndInsertValues()
    Application.ScreenUpdating = False
   
    Dim rf, rf2 As Range
    Dim results(11) As String
   
    Range("K3:T35").ClearContents
    endCol = 75
    rowFill = 3
    criteriaY = Sheets("Sheet3").Cells(1, 1)
    Sheets("Sheet1").Activate
    Range("BD2:BW2").Select
   
    Set rf1 = Selection.Find(What:=criteriaY, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not rf1 Is Nothing Then
        startCol = rf1.Column
        For counterRowsToFill = startCol To endCol
       
            For counterX = 11 To 20
                criteriaX = Sheets("Sheet3").Cells(2, counterX)
               
                Range("BD2:BD23").Select
                Set rf2 = Selection.Find(What:=criteriaX, After:=ActiveCell)
                If Not rf2 Is Nothing Then
                    results(counterX - 10) = Cells(rf2.Row, startCol)
                End If
   
            Next counterX
                       
             Sheets("Sheet3").Activate
                       
            For counter = 11 To 20
                Cells(rowFill, counter) = results(counter - 10)
            Next counter
           
            rowFill = rowFill + 1
            startCol = startCol + 1
            Sheets("Sheet1").Activate
        Next counterRowsToFill
    End If
    Sheets("Sheet3").Activate
    Application.ScreenUpdating = True

End Sub
0
 

Author Closing Comment

by:sandramac
ID: 39263924
Thanks it worked out great.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39263992
Clearly I didn't understand the requirements.  Some things I would suggest for tidying up the code submitted is:

1) Fully qualify your Range objects with worksheet objects.
2) Avoid activating sheets or cells, it's not necessary (must complete 1 above for this to not fail).
3) Prior to setting "rf2" range object, set it to Nothing, so each iteration isn't based on the previous one if not found on the current loop.
4) Declare your variables properly, for example...
"Dim rf, rf2 As Range" sets "rf" as a Variant type.  Colin Legg just posted a blog about this exact issue: http://colinlegg.wordpress.com/2013/06/08/a-common-mistake-when-declaring-variables-in-vba/

HTH
Zack
0
 

Author Comment

by:sandramac
ID: 39264051
I do have another requirement with the coding, i will post a new question for this, thanks firefytr and noExpert for your help...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

930 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

14 Experts available now in Live!

Get 1:1 Help Now