User Form to Find Row With Data from More than One Column 2

[shelby55]

Hello

I would like to create a dialog box or user form to enter in data and then a button so the user is taken to the row matching the input.

The input is a combination of two cells - CMG and RIL which are text fields. I can build the user form but not able to create code that sends the user to the row matching the input criteria of the user form.

Any assistance appreciated - thanks.

 

[PHV]

Are the leading zeroes preserved in the autofilter combo when you try the AutoFilter manually ?
If not then replace this in my code:
Criteria1:=Format(lCMG, "000")
with this:
Criteria1:=lCMG

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Does your table start in A1?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

okay, if your table does not start in A1, then use this more general procedure, using PHV's code...

Sub SelectRow(lCMG As Long, lRIL As Long)
    Dim lRow As Long
    With ActiveSheet.UsedRange
        .AutoFilter _
                Field:=.Rows(1).Find("CMG").Column - .Column + 1, _
                Criteria1:=Format(lCMG, "000")
        .AutoFilter _
                Field:=.Rows(1).Find("RIL").Column - .Column + 1, _
                Criteria1:=lRIL
        lRow = Intersect(.Cells, .Range(.Cells(2, 1), .Cells(.Rows.Count, 1)).EntireRow).SpecialCells(xlCellTypeVisible).Row
        ActiveSheet.ShowAllData
        ActiveSheet.Rows(lRow).Select
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Could your data in CMG or RIL have leading or trailing spaces?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Thanks for your responses. To answer PHV, if I want to use "find" in Excel to look up a CMG I would have to enter "001" and not just "1", so the preceding zeros are preserved. None will have trailing or leading spaces.

For the data that I provided, the CMG column is A and the row title of "CMG" starts in A1. All subsequent columns are in order i.e. B for CMG Description, C for RIL etc.

I used PHV's code (the newest) and I still get an error on the line

lRow = Intersect(.Cells, .Range(.Cells(2, 1), .Cells(.Rows.Count, 1)).EntireRow).SpecialCells(xlCellTypeVisible).Row

So not sure why this wouldn't be working?

 

[SkipVought]

The invitation is still open, if you wish to send.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry I didn't see that, yes I'd like to take you up on that. No need to scrub data, the information is public. Same e-mail as in 2009?

 

[SkipVought]

Yes

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

There are many significant peices of information that we had to tease out during the duration of this thread.

One signoficant piece of information was in the ACTUAL heading values of the fields in your table. The first heading value is CMG. The second heading value is CMGDesc. A...HAH!!! That makes a difference in how we look for CMG in the first heading (and the reason that we just don't assume that CMG is in column 1 and RIL is in column 3, is that someday you or someone else might change the location or order of headings). So if we just do a plain vanella Find, starting in Row(1) it looks beyond the first cell and finds the second cell in Row(1). We have to tell the Find method to search for the WHOLE value...

Sub SelectRow(lCMG As Long, lRIL As Long)
    Dim lRow As Long
    With ActiveSheet.UsedRange
        .AutoFilter _
                Field:=.Rows(1).Find(what:="CMG"[b], LookAt:=xlWhole[/b]).Column - .Column + 1, _
                Criteria1:=Format(lCMG, "000")
        .AutoFilter _
                Field:=.Rows(1).Find("RIL").Column - .Column + 1, _
                Criteria1:=lRIL
        lRow = Intersect(.Cells, .Range(.Cells(2, 1), .Cells(.Rows.Count, 1)).EntireRow).SpecialCells(xlCellTypeVisible).Row
        ActiveSheet.ShowAllData
        ActiveSheet.Rows(lRow).Select
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Ta da! Works great, thanks very much for sticking with me Skip and PHV!