Need a lookup solution.

[a15457]

Hello all,

I have a workbook that is basically an export from DNS in one of my customers environments. As there's no good simple lookup / find function in MS AD DNS I'm looking to Excel 2013 for a solution.

So far what I have done is bring all the separate zone files into one excel workbook, so now my workbook has 90+ tabs in it. What I want to do is have a "Coverpage" type sheet with one cell in it that I can enter a value that I'm trying to find and then have some rows below where after the search is run the results are displayed. Hopefully I can make this a little clearer.

Lets say that I enter the value that I'm looking for into $A$5 of Sheet 1. Let's call that value "ServerDC001".

What I want to happen next is for excel to search all the other tabs in the workbook and return in rows 10 down all the corresponding rows from the other TABs. So if there were three instances of ServerDC001 found in the workbook return the contents of the entire row where the value was found to Row 10, 11, and 12. Something like: (Where there is a comma in the sample data below is a separate column.)

Row 10
5f56fb8c-3f70-436a-ba4d-00a6f05c4a18,CNAME,8/24/2016,8:00:00 AM,0:10:00,ServerDC001.domainname.com.
Row 11
@,NS,0,1:00:00,ServerDC001.domainname.com.
Row 12
ServerDC001,A,0,1:00:00,10.143.154.19

If there were 100 instances found then I want them all listed.

So you can see from the sample data that the search value can lie in one of three different columns which I guess makes things a little trickier.

What I'm hoping for at the moment is some hints and tips or pointers in the right direction to find a solution for my problem.

I've done a bit of VBA coding before and I suspect that this is where I'm going to have to go to get the solution but I'm going to need some help to get there.

Hopefully someone can point me in the right direction.
Kind regards,

Dave

 

[CalcSux78]

Hi Dave...

I had a similar function built into a workbook I made a few years back. Here's what I'd do:

On your first sheet, decide what cell you will type your search item in (will be used in code below).

• Go to the Developer tab & click Insert > ActiveX > Listbox. (this should be the 4th item in the bottom section of the Insert Dialogue box that appears)
• With the new Listbox selected, click on Properties (on the ribbon) and change the ColumnCount = 3.
• Right-Click on the tab name of your search sheet and select "View Code"
• Paste the following code:

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Option Explicit
Option Compare Text




Private Sub ListBox1_Click()
Dim N As Integer
Dim sh As Worksheet


With ListBox1
    For N = 0 To .ListCount - 1
        If .Selected(N) = True Then
            Set sh = ThisWorkbook.Sheets(.List(N, 0))
            sh.Select
            sh.Range(.List(N, 1)).Select
        End If
    Next N
End With
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim N As Integer, R As Integer
Dim sh As Worksheet
Dim cel As Range
Dim rTarg As Range
Dim What As Variant


Set rTarg = [F2] ' Change this to your search cell


If Not Intersect(Target, rTarg) Is Nothing Then
    ListBox1.Clear
    What = "*" & rTarg.Value & "*"
    For Each sh In ThisWorkbook.Sheets
        If Not sh.Index = 1 Then    'don't search first sheet
            For Each cel In sh.UsedRange
                If CStr(cel) Like What Then  '   I'm LMFAO right now
                    With ListBox1
                        .AddItem sh.Name
                        .List(R, 1) = cel.Address(0, 0)
                        .List(R, 2) = cel.Value
                        R = R + 1
                    End With
                End If
            Next cel
        End If
    Next sh
End If
End Sub
[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]

Once complete, you can close the Editor, un-select "Design Mode" on the developer tab and test away.

 

[a15457]

Duuuuuuuuude!

That's AMAZING!!!

Works even better than I was hoping!

The only problem that I have is that with 90+ sheets in a tab when I go to look at the reference I have a loooooooong scroll back to the home sheet. I can live with that though!!!

Cheers,

Dave

 

[CalcSux78]

That's an easy one....

Add this to a standard Module (if you haven't added one yet, then in VBEditor, go to Insert > Module.)

Sub GoHome()
Sheets(1).Activate
End Sub

Back in the workbook on the developer tab, select macro and Options. There, you can assign a keyboard shortcut to jump back to the first sheet (I like to use [CTRL+Shift+Z])

 

[a15457]

Found an easier option just after I posted that last message ...

down the bottom left where you have the left arrow, right arrow, and the three dots ...

If you hold the CTRL key and click the left arrow it goes to the first TAB.

Win!!!

Cheers,

Dave