?
Solved

Match Values and Return to Cell

Posted on 2016-10-19
6
Medium Priority
?
44 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

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!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

770 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