Lookup value from list, return surrounding data.

[Cicada]

Hello

I have a list of names on sheet 3. On sheet 2 a large data set. I'm trying to figure out vba that will look for every occurrence of each name in sheet 2 (only in column E) and return a range of values or block of cells surrounding each occurrence into sheet 4. To illustrate what i mean i'll give an example. One name in the list in sheet 3 is Peter i would like the vba to look for every occurrence of Peter in sheet 2 lets say the first occurs in E24 I would like the values in D24 (the cell just to its left) F24,G24,H24 (3 cells to the right) and D25,E25,F25,G25,H25 to be returned into sheet 4 (sorry wasn't sure how to reference those) and the same for the next occurrence. The name will always occur in column E i would like all other instances of each name that is not in E to be ignored. The data will always be Column D to H on the row of the occurrence and the D to H on the row below. I dont even know where to begin. Any help would be greatly appreciated. Regards

Dan

[jaslake]

Hi Dan

A sample file of your data would be helpful (with before and after) if you wish to receive tested code.

Couple of questions:
If "Peter" is in Cell E24 what information is in E25 (what text does E25 contain..."Peter"?)
Where should the collected information be placed in Sheet4?
The list of Names in Sheet3...are they unique?
Do you wish to search for ALL the names listed in Sheet3 or do you wish to choose the name to search for?

[Cicada]

Thanks for the reply Jaslake,

Sorry it has taken me so long to get back to you. Please see attached a sample workbook. The data set would usually be much larger though it will always be arranged in exactly the same way. As you can see there are a list of names in sheet three, that all appear in sheet 2. What i would like is a code that would find specific occurrences of each name and return the data around it into sheet 4. To elaborate, take the first name Scooby Blue, as you can see in sheet 2 it first appears in D17 and below that there is historical data for that name. if successfully executed the code should return the following ranges for that name D24:H25, D34:H35, D44:H45, D54:H55 and D64:H65. It will not always be evenly spaced like this, the second name Cornish Legend would be ranges D110:H111, D116:H117 and so on. Cornish Legend also helps to illustrate something I wish to avoid, as an example that name appears also in E542 under Jojon's Data (as they have raced before this happens quite frequently) I would like for those instances to be ignored entirely. In summary I would like only each occurrence of each name (and the surrounding data) as it occurs under their own banner you will see that D17 is scooby blue D103 Cornish Legend D189 Out Jogging and so forth. they occur exactly 86 rows apart. Sorry to be so long winded just wanted to be clear. As for where i would like this data. i would like it to be returned into sheet 4 first result in range A1:E2 the next below that and so on in order until finished. If you have gotten this far, thankyou. Thanks very much for your time. I look forward to your response.

Regards
Dan

[stanleydgromjr]

Cicada,

Detach/open workbook GetNames w2 column D merged cells to w3 - Cicada - EF783594 - SDG15.xlsm and run macro GetNames.


If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetNames()
' stanleydgromjr, 07/15/2011
' http://www.excelforum.com/excel-programming/783594-vba-return-values-offset-loop-issues.html
Dim w2 As Worksheet, w3 As Worksheet
Dim c As Range, firstaddress As String, LR As Long, NR As Long
Application.ScreenUpdating = False
Set w2 = Worksheets("Sheet2")
LR = w2.Cells(Rows.Count, 4).End(xlUp).Row
Set w3 = Worksheets("Sheet3")
NR = w3.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
If NR = 2 And w3.Cells(1, 1) = "" Then NR = 1
With w2.Range("D1:I" & LR)
  Set c = .Find(" GREYHOUNDS RACECARDS", LookIn:=xlValues)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      w3.Range("A" & NR).Value = c.Offset(4).Value
      NR = w3.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
w3.Columns(1).AutoFit
w3.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the GetNames macro.

[jaslake]

Hi Dan

In addition to stanleydgromjr's code (slightly modified), this code is in the attached...see if it does as you require. Run GetNames procedure...it then calls GetData procedure.

Sub GetData()
    Dim w2 As Worksheet, w4 As Worksheet
    Dim LR As Long
    Dim Rng As Range
    Dim lRng As Range
    Dim nCell As Range
    Dim rFoundCell As Range
    Dim lCount As Long
    Dim x As String
    Dim y As String
    Dim NR As Long
    Set w2 = Worksheets("Sheet2")
    LR = w2.Cells(Rows.Count, 4).End(xlUp).Row
    Set w4 = Worksheets("Sheet4")
    Set Rng = Range("Names")
    For Each nCell In Rng
        Set lRng = w2.Range("D:D").Find(What:=nCell, _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                MatchCase:=False)
        If Not lRng Is Nothing Then
            x = lRng.Row
            y = x + 75
        End If
        Set rFoundCell = w2.Range("E" & x)
        If Not WorksheetFunction.CountIf(w2.Range("E" & x & ":E" & y), nCell) = 0 Then
            For lCount = 1 To WorksheetFunction.CountIf(w2.Range("E" & x & ":E" & y), nCell)
                Set rFoundCell = Columns(5).Find(What:=nCell, After:=rFoundCell, _
                        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=False)
                With rFoundCell
                    NR = w4.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
                    If NR = 2 And w4.Cells(1, 1) = "" Then NR = 1
                    w4.Range("A" & NR).Resize(2, 5).Value = _
                            rFoundCell.Offset(0, -1).Resize(2, 5).Value
                End With
            Next lCount
        Else
            MsgBox "No records found for " & nCell
        End If
    Next nCell
    w4.Columns.AutoFit
End Sub

[Cicada]

Thanks for the responses I will check it out tonight. To the mod, I'm not sure why this has been merged into one thread i was asking for two different things in each of them. Thanks Stanley and Jaslake for your time much appreciated. Will let you know how i get along.

Regards
Dan

[Cicada]

Hey Stanley and Jaslake,

Thanks again for your responses and again sorry for the late reply life is crazy at the moment. I ran those two bits of code, the first by stanley works like a charm, so quick. The second i am having some difficulty when i run it i get a runtime error 1004 Method 'Range' of object'_Global failed, when i go to debug the following is highlighted

Set Rng = Range("Names")

. Not sure what to do. Thanks again.

Regards
Dan

[jaslake]

Hi Dan

I'm unable to duplicate this error running the code in Test#1.xlsm

i get a runtime error 1004 Method 'Range' of object'_Global failed

It indicates to me that perhaps your running Stan's original version of the code. As I previously mentioned I modified his code slightly to create a Named Range called "Names".

Look at the code in Test#1.xlsm...run GetNames ONLY (GetNames calls the procedure I wrote...GetData)...see if Sheet4 has the results you desire.

[Cicada]

Truly awesome, thanks Jaslake, even reading your reponse was confusing for me, i have no idea how you guys come up with this stuff. Works like a charm. perfect everything i need and where i want it. You were right i ran the original code, then tried the modified though ran both at once got it in the end though. Thankyou very much to you both for your time, much appreciated. Been trying to get this for a while, this forum far and away the best of the lot.

Regards
Dan

[jaslake]

Hi Dan

Glad it works for you. If that satisfies your need, please mark your thread as "Solved".

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save