Match Values and Return to Cell

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.
Who is Participating?
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
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.

Glenn RayExcel VBA DeveloperCommented:
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
    '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
    Do Until ActiveCell.Value = ""
        'copy/transpose mod values
        Set rngMod = Range(ActiveCell, ActiveCell.Offset(3, 0))
        Range("A" & lngDestRow & ":A" & lngDestRow + intProdCats - 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        'copy over Prod values
        Set rngProd = Union(Range("A8:B" & 7 + intProdCats), _
            Range(ActiveCell.Offset(6, 0), ActiveCell.Offset(5 + intProdCats, 0)))
        Range("E" & lngDestRow).PasteSpecial Paste:=xlPasteValues
        lngDestRow = lngDestRow + intProdCats
        'move to next mod column
        ActiveCell.Offset(0, 1).Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Done."
End Sub

Open in new window

Let me know if you have any questions.
MATO0618Author Commented:
Working great. I'd really like to have the output to be on another worksheet (tab). Other than that its perfect.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Glenn RayExcel VBA DeveloperCommented:
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.
MATO0618Author Commented:
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.

MATO0618Author Commented:
Great help. Thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.