Avatar of cd_morris
cd_morrisFlag for United States of America asked on

Do a vlookup on a merge cell and copy the 5 row below to another sheet

How can you do a vlookup on a merge cell and copy the 5 row below and append to the bottom of another sheet?

My example:
I have a sheet name (for an example "Data") with classes from say Class 1 to Class 55.

I have a check box sheet that when I check the box, I want to do a vlookup for say "Class 1" on sheet Data and copy the header and the 5 row below and append to sheet "Results"
Sample-Spreadsheet.xlsx
Microsoft Excel

Avatar of undefined
Last Comment
SiddharthRout

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
SiddharthRout

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SiddharthRout

Here is a quick example based on your workbook.

Hope this helps

Sid
Sample.xlsm
Jignesh Thar

cd_morris - if you'd like formula based approch (you can use this formula in VBA as well), please see attached
Sample-Spreadsheet-Jignesh-Edite.xlsx
Patrick Matthews

SiddharthRout and jigneshthar,

Posting sample workbooks is a wonderful thing.  However, whenever you do, as a courtesy to all who may be reading the threads you participate in, please also post the formulas and/or VBA code into your comment as well.  There are two reasons for this request:

The contents of file attachments are not visible to EE's search functions, so getting the code/formulas into the comment helps make your solution more searchable
This way people do not have to download the file to get an idea of what you are proposing

Thanks!

Patrick
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SiddharthRout

Point Taken Patrick :)

Private Sub CheckBox1_Click()
    If CheckBox1.Value = False Then Exit Sub
    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow As Long
    Dim aCell As Range, bCell As Range
    Dim strTemp As String
    
    On Error GoTo Err
    
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("Data")
    
    lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 3
    
    Set aCell = ws2.Cells.Find(What:=Trim(CheckBox1.Caption), LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then
        strTemp = aCell.Address & ":" & aCell.Offset(5).Offset(, 4).Address
    End If
    
    ws2.Range(strTemp).Copy
    ws1.Range("A" & lastRow).Select
    ws1.Paste
    
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Open in new window


Sid
ASKER
cd_morris

Excellent thank you
SiddharthRout

You are welcome :)

Sid
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.