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.

 

[SkipVought]

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

what this is doing is making the intersection with 1) the UsedRange on the sheet and 2) the range A2:XDF1048576 for ONLY VISIBLE CELLS and for that cell (and I presume that it is ONLY ONE CELL) assign the Row Number property.

Skip,

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

 

[shelby55]

Hi Skip

It's still not working and i get the same message. I created a field where I concatenated the two cells together. It was hidden but even when visible the code doesn't work.

Note that the filter is applied but hides all rows because the criteria doesn't apply.

What am I missing? Thanks.

 

[SkipVought]

Note that the filter is applied but hides all rows because the criteria doesn't apply.

So exactly how should these two values be used to find a row.

Need to see a cogent example that represents your data and how these two values result in ONE ROW selected?

Skip,

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

 

[shelby55]

Hi Skip

The data is as below. The descriptions are too long so I replaced them with what I've got but the descriptions are the same per CMG. There are length of stay targets per age group so I want to enter in CMG and RIL to get be able to select the correct LOS target. I excluded the combined column previously discussed because I'd prefer to not have to use it but I do then it would be on the last column. So the combo of CMG and RIL is a unique combination.

CMG Desc RIL Age<18 AgeA AgeB AgeC
001 aaa 1 4.6 3.6 4.7 0.0
001 aaa 2 6.0 13.6 16.2 0.0
001 aaa 3 0.0 11.0 14.0 0.0
001 aaa 4 9.0 18.0 8.0 0.0
001 aaa 5 0.0 21.7 27.0 53.0
001 aaa 6 40.0 56.0 18.0 0.0
002 bbb 1 1.0 2.0 1.3 1.0
002 bbb 2 1.0 6.7 9.8 4.0
002 bbb 3 0.0 10.7 11.0 0.0
002 bbb 4 0.0 11.0 7.0 0.0
002 bbb 5 0.0 18.0 10.0 26.0
002 bbb 6 0.0 32.0 37.0 0.0
003 ccc 1 2.0 1.5 1.5 2.0
003 ccc 2 2.0 8.0 4.8 4.0
003 ccc 3 0.0 6.0 4.0 12.0
003 ccc 4 0.0 19.0 9.0 0.0
003 ccc 5 0.0 9.0 19.0 28.0
003 ccc 6 0.0 0.0 31.0 48.0

Does this help? Thanks.

 

[SkipVought]

I want to enter in CMG and RIL to get be able to select the correct LOS target.

So exactly what CMG & RIL to get WHICH LOS target?

Skip,

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

 

[SkipVought]

I took your example and I used the following calling routine to call my testSelectRow procedure.

IT WORKED!

Sub testt()
    testSelectRow "001", "6"
End Sub

This selected...
[tt]
CMG Desc RIL Age<18 AgeA AgeB AgeC
001 aaa 1 4.6 3.6 4.7 0.0
001 aaa 2 6.0 13.6 16.2 0.0
001 aaa 3 0.0 11.0 14.0 0.0
001 aaa 4 9.0 18.0 8.0 0.0
001 aaa 5 0.0 21.7 27.0 53.0
[highlight]001 aaa 6 40.0 56.0 18.0 0.0[/highlight]
002 bbb 1 1.0 2.0 1.3 1.0
002 bbb 2 1.0 6.7 9.8 4.0
002 bbb 3 0.0 10.7 11.0 0.0
002 bbb 4 0.0 11.0 7.0 0.0
002 bbb 5 0.0 18.0 10.0 26.0
002 bbb 6 0.0 32.0 37.0 0.0
003 ccc 1 2.0 1.5 1.5 2.0
003 ccc 2 2.0 8.0 4.8 4.0
003 ccc 3 0.0 6.0 4.0 12.0
003 ccc 4 0.0 19.0 9.0 0.0
003 ccc 5 0.0 9.0 19.0 28.0
003 ccc 6 0.0 0.0 31.0 48.0

[/tt]


Skip,

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

 

[SkipVought]

I'd be willing to wager that because you originally stated, "The input is a combination of two cells - CMG and RIL which are text fields," in fact, RIL is NOT a text field! Just a guess.

Skip,

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

 

[PHV]

This works for me with your posted data:

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

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

 

[SkipVought]

@PHV, the user did state, "CMG and RIL which are text fields"

Skip,

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

 

[PHV]

I agree Skip, but the posted data are obviously numeric ...

 

[SkipVought]

001 is not necessarily numeric.

This cones down to poorly communicated specifications.

Skip,

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

 

[SkipVought]

Actually, PHV, your solution did NOT work on the data I imported, because, according to standard oeration procedure, I imported column CMG as TEXT since that is how the user represented the data.

As such the LONG on that column is a mismatch!

Skip,

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

 

[PHV]

Skip, I used this: Criteria1:=Format(lCMG, "000") which is Text

 

[SkipVought]

touche, PHV! I failed to copy your code!

I'd suggest another much simpler solution, using Conditional Formatting.

When your make your selection, write each result to a cell RangeNamed CMG & RIL

The use this CF formula
[tt]
=AND($A2=CMG,$C2=RIL)
[/tt]
where column A and column C are the columns in question.

Shade the cell interior as the format.

Skip,

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

 

[shelby55]

Whoa, you've been busy! Thanks guys for the updates but using PHV's code I couldn't get it to work. Just an FYI that RIL appears as a number but isn't formatted as such in the worksheet nor is CMG. They are text fields as previously stated.

Skip, where is the selection being made, still in the User Form?

Thanks.

 

[PHV]

Note also that I used the above and added code to set the values for CMG and RIL and when I ran it, it didn't work.
Which code did you add to test the sub ?

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

 

[SkipVought]

I simply changed the argument data types and never looked any further. Sorry!

Skip,

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

 

[SkipVought]

Don't care WHERE the selection is made. What matters is the values passed to the procedure!

Please note that merely formatting a column as TEXT, does NOTHING to make the values TEXT!!!


Skip,

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

 

[shelby55]

Hi

PHV, I used your code:

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

Then on the cmdFind button I used:

Private Sub cmdFind_Click()
SelectRow CMG.Value, RIL.Value

End Sub

I still get the error that "no cells were found".

 

[SkipVought]

Can you scrub your data and email me the workbook?

Skip,

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