Solved

Paste Data

Posted on 2013-06-19
9
256 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Security Threats Are You Missing?

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

707 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

16 Experts available now in Live!

Get 1:1 Help Now