Solved

Match Values and Return to Cell

Posted on 2016-10-19
6
40 Views
Last Modified: 2016-10-21
See attached:

I need to lookup the values of Range C2:C5, then return values of Range A8:D8, for each instance. I have hundreds of columns, and rows. The rows and columns will change from file to file. I'd like to be able to find a way to do this with VBA.

as shown on attachment.
C--Users-SZYYNR-Documents-TESTMATCH.xlsx
0
Comment
Question by:MATO0618
[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
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41850805
The following code will transpose and copy the values in the columns in rows 2:5 and their respective PROD and AMT values in rows 8:10 (or further down, at least as far as row 19).  It will clear any data in the OUTPUT section (bound on upper left by cell A24) before pasting in values.  I've added an additional "brand" on top to test for extensibility.  You can have brands as far as column CA in this example.

Option Explicit
Sub Transfer_Data()
    Dim intMods, intProdCats As Integer
    Dim rngMod, rngProd As Range
    Dim lngDestRow As Long
    
    Application.ScreenUpdating = False
    
    'Clear output area
    Range("A24:G10000").Clear
    
    'set number of mods (in columns on top) and PROD categories (in rows)
    intMods = WorksheetFunction.CountA(Range("C2:CA2"))
    intProdCats = WorksheetFunction.CountA(Range("A8:A19"))
    lngDestRow = 24
    
    Range("C2").Select
    Do Until ActiveCell.Value = ""
        'copy/transpose mod values
        Set rngMod = Range(ActiveCell, ActiveCell.Offset(3, 0))
        rngMod.Copy
        Range("A" & lngDestRow & ":A" & lngDestRow + intProdCats - 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        rngMod.Select
        
        'copy over Prod values
        Set rngProd = Union(Range("A8:B" & 7 + intProdCats), _
            Range(ActiveCell.Offset(6, 0), ActiveCell.Offset(5 + intProdCats, 0)))
        rngProd.Copy
        Range("E" & lngDestRow).PasteSpecial Paste:=xlPasteValues
        lngDestRow = lngDestRow + intProdCats
        rngMod.Select
        
        'move to next mod column
        ActiveCell.Offset(0, 1).Select
    Loop
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Done."
End Sub

Open in new window


Let me know if you have any questions.
-Glenn
EE-C-Users-SZYYNR-Documents-TESTMAT.xlsm
0
 

Author Comment

by:MATO0618
ID: 41850975
Working great. I'd really like to have the output to be on another worksheet (tab). Other than that its perfect.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41851044
I'd really like to have the output to be on another worksheet (tab). Other than that its perfect.

That can be arranged.  Specify the sheet name and layout location and I can revise the code.
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!

 

Author Comment

by:MATO0618
ID: 41851095
I've reattached the worksheet with the destination tab added (A1XX Output), and I'd like the data headers to start at Cell A1 as indicated in the file.

Thanks
EE-C-Users-SZYYNR-Documents-TESTMAT.xlsm
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41851186
Here you go.  I've revised the code to paste the data into the [A1XX Output] sheet.  It's blank now; you can test it by pressing the "Transfer Data" button.

Regards,
-Glenn
EE-C-Users-SZYYNR-Documents-TESTMAT.xlsm
0
 

Author Closing Comment

by:MATO0618
ID: 41854343
Great help. Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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