Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
rngFound not finding correct value when more than one match
The below (Last Code)code finds a specified value selected from a form in LISTBOX3(when clicked)
But IF there is 1 value in LISTBOX3 the same i ONLY get the last value. How can i do the same as below but with a : If rngFound.Offset(0-1).value = LISTBOX2.value and rngFound.value = LISTBOX3.value then blah blah blah... The form i created uses 3 x LISTBOXES to narrow down the required value, LISTBOX population codes as follows: LISTBOX1 Private Sub UserForm_Initialize() Application.ScreenUpdating = False Dim LastCell As Long Dim myrow As Long Dim NoDupes As Collection On Error Resume Next Sheets("Data").Visible = True LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") ..Select Set NoDupes = New Collection For myrow = 1 To LastCell If .Cells(myrow, 1) < "" Then NoDupes.Add .Cells(myrow, 1).Value, CStr(.Cells(myrow, 1).Value) If Err.Number = 0 Then ListBox1.AddItem Cells(myrow, 1).Value End If Err.Clear End If Next End With Sheets("Data").Visible = False Sheets("JSA Procedure").Activate Application.ScreenUpdating = True End Sub LISTBOX2 Private Sub ListBox1_Click() Dim LastCell As Long Dim myrow As Long Dim NoDupes As Collection Set NoDupes = New Collection Application.ScreenUpdating = False If ListBox2.ListCount 0 Then ListBox2.Clear LastCell = Worksheets("Data").Cells(Rows.Count, "C").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") For myrow = 1 To LastCell If ListBox1.Value = .Cells(myrow, 1).Value And .Cells(myrow, 1) < "" Then On Error Resume Next NoDupes.Add .Cells(myrow, 3).Value, CStr(.Cells(myrow, 3).Value) If Err.Number = 0 Then If .Cells(myrow, 1).Offset(, 2).Value < "" And .Cells(myrow, 1) = ListBox1.Value Then ListBox2.AddItem .Cells(myrow, 1).Offset(, 2).Value ' <=== DUPLICATES ALLOWED On Error GoTo 0 End If End If End If Next End With Sheets("JSA Procedure").Select Application.ScreenUpdating = True End Sub LISTBOX3 Private Sub ListBox2_Click() If ListBox2.Value < "" Then ListBox3.Clear Dim LastCell As Long Dim myrow As Long Sheets("Data").Visible = True LastCell = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row With ActiveWorkbook.Worksheets("Data") ..Select 'first thing to do with a With statement that occurs on a second sheet For myrow = 1 To LastCell If .Cells(myrow, 2) < "" Then If ListBox1.Value = .Cells(myrow, 1) And ListBox2.Value = .Cells(myrow, 1).Offset(, 2).Value Then ListBox3.AddItem .Cells(myrow, 1).Offset(0, 1).Value End If End If Next End With End If Sheets("JSA Procedure").Select Application.ScreenUpdating = True End Sub THE FIND Code is run when the User click's on a value inm the LISTBOX3 as below: Private Sub ListBox3_Click() Application.ScreenUpdating = False If ListBox1.Value < "" And ListBox2.Value < "" Then ' Place data into sheet Dim rngFound As Range On Error Resume Next ' Gain the Location of the Listbox Value Sheets("Data").Visible With Worksheets("Data").Range("B:B") Set rngFound = .Find(What:=Me.ListBox3.Value, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False) If rngFound.Value < "" Then Sheets("JSA Procedure").Unprotect Sheets("JSA Procedure").Range("L2").Value = rngFound Sheets("JSA Procedure").Range("D2").Value = rngFound.Offset(, -1) Sheets("JSA Procedure").Range("D4").Value = rngFound.Offset(, 1) Sheets("JSA Procedure").Range("L4").Value = rngFound.Offset(, 2) Sheets("JSA Procedure").Range("P4").Value = rngFound.Offset(, 3) Sheets("JSA Procedure").Range("T4").Value = rngFound.Offset(, 4) Sheets("JSA Procedure").Range("D6").Value = rngFound.Offset(, 5) Sheets("JSA Procedure").Range("N6").Value = rngFound.Offset(, 6) Sheets("JSA Procedure").Range("T6").Value = rngFound.Offset(, 7) Sheets("JSA Procedure").Range("E8").Value = rngFound.Offset(, 8) Sheets("JSA Procedure").Range("N8").Value = rngFound.Offset(, 9) Sheets("JSA Procedure").Range("T8").Value = rngFound.Offset(, 10) Sheets("JSA Procedure").Range("E10").Value = rngFound.Offset(, 11) Sheets("JSA Procedure").Range("E12").Value = rngFound.Offset(, 12) Sheets("JSA Procedure").Range("B14").Value = rngFound.Offset(, 13) Sheets("JSA Procedure").Range("O14").Value = rngFound.Offset(, 14) Sheets("JSA Procedure").Range("B18").Value = rngFound.Offset(, 15) Sheets("JSA Procedure").Range("O18").Value = rngFound.Offset(, 16) Sheets("JSA Procedure").Range("B20").Value = rngFound.Offset(, 17) Sheets("JSA Procedure").Range("O20").Value = rngFound.Offset(, 18) Sheets("JSA Procedure").Range("B23").Value = rngFound.Offset(, 19) Sheets("JSA Procedure").Range("B31").Value = rngFound.Offset(, 20) Sheets("JSA Procedure").Range("B50").Value = rngFound.Offset(, 21) Sheets("JSA Procedure").Range("F50").Value = rngFound.Offset(, 22) Sheets("JSA Procedure").Range("J50").Value = rngFound.Offset(, 23) Sheets("JSA Procedure").Range("O50").Value = rngFound.Offset(, 24) Sheets("JSA Procedure").Range("R50").Value = rngFound.Offset(, 25) Sheets("JSA Procedure").Range("V50").Value = rngFound.Offset(, 26) Sheets("JSA Procedure").Range("B52").Value = rngFound.Offset(, 27) Sheets("JSA Procedure").Range("F52").Value = rngFound.Offset(, 28) Sheets("JSA Procedure").Range("J52").Value = rngFound.Offset(, 29) Sheets("JSA Procedure").Range("O52").Value = rngFound.Offset(, 30) Sheets("JSA Procedure").Range("R52").Value = rngFound.Offset(, 31) Sheets("JSA Procedure").Range("V52").Value = rngFound.Offset(, 32) Sheets("JSA Procedure").Range("B54").Value = rngFound.Offset(, 33) Sheets("JSA Procedure").Range("F54").Value = rngFound.Offset(, 34) Sheets("JSA Procedure").Range("J54").Value = rngFound.Offset(, 35) Sheets("JSA Procedure").Range("O54").Value = rngFound.Offset(, 36) Sheets("JSA Procedure").Range("R54").Value = rngFound.Offset(, 37) Sheets("JSA Procedure").Range("V54").Value = rngFound.Offset(, 38) Sheets("JSA Procedure").Range("B56").Value = rngFound.Offset(, 39) Sheets("JSA Procedure").Range("F56").Value = rngFound.Offset(, 40) Sheets("JSA Procedure").Range("J56").Value = rngFound.Offset(, 41) Sheets("JSA Procedure").Range("O56").Value = rngFound.Offset(, 42) Sheets("JSA Procedure").Range("R56").Value = rngFound.Offset(, 43) Sheets("JSA Procedure").Range("V56").Value = rngFound.Offset(, 44) Sheets("JSA Procedure").Range("B58").Value = rngFound.Offset(, 45) Sheets("JSA Procedure").Range("F58").Value = rngFound.Offset(, 46) Sheets("JSA Procedure").Range("J58").Value = rngFound.Offset(, 47) Sheets("JSA Procedure").Range("O58").Value = rngFound.Offset(, 48) Sheets("JSA Procedure").Range("R58").Value = rngFound.Offset(, 49) Sheets("JSA Procedure").Range("V58").Value = rngFound.Offset(, 50) Sheets("JSA Procedure").Range("B60").Value = rngFound.Offset(, 51) Sheets("JSA Procedure").Range("F60").Value = rngFound.Offset(, 52) Sheets("JSA Procedure").Range("J60").Value = rngFound.Offset(, 53) Sheets("JSA Procedure").Range("O60").Value = rngFound.Offset(, 54) Sheets("JSA Procedure").Range("R60").Value = rngFound.Offset(, 55) Sheets("JSA Procedure").Range("V60").Value = rngFound.Offset(, 56) Sheets("JSA Procedure").Range("B62").Value = rngFound.Offset(, 57) Sheets("JSA Procedure").Range("F62").Value = rngFound.Offset(, 58) Sheets("JSA Procedure").Range("J62").Value = rngFound.Offset(, 59) Sheets("JSA Procedure").Range("O62").Value = rngFound.Offset(, 60) Sheets("JSA Procedure").Range("R62").Value = rngFound.Offset(, 61) Sheets("JSA Procedure").Range("V62").Value = rngFound.Offset(, 62) Sheets("JSA Procedure").Range("B64").Value = rngFound.Offset(, 63) Sheets("JSA Procedure").Range("F64").Value = rngFound.Offset(, 64) Sheets("JSA Procedure").Range("J64").Value = rngFound.Offset(, 65) Sheets("JSA Procedure").Range("O64").Value = rngFound.Offset(, 66) Sheets("JSA Procedure").Range("R64").Value = rngFound.Offset(, 67) Sheets("JSA Procedure").Range("V64").Value = rngFound.Offset(, 68) Sheets("JSA Procedure").Range("B66").Value = rngFound.Offset(, 69) Sheets("JSA Procedure").Range("F66").Value = rngFound.Offset(, 70) Sheets("JSA Procedure").Range("J66").Value = rngFound.Offset(, 71) Sheets("JSA Procedure").Range("O66").Value = rngFound.Offset(, 72) Sheets("JSA Procedure").Range("R66").Value = rngFound.Offset(, 73) Sheets("JSA Procedure").Range("V66").Value = rngFound.Offset(, 74) Sheets("JSA Procedure").Range("B68").Value = rngFound.Offset(, 75) Sheets("JSA Procedure").Range("F68").Value = rngFound.Offset(, 76) Sheets("JSA Procedure").Range("J68").Value = rngFound.Offset(, 77) Sheets("JSA Procedure").Range("O68").Value = rngFound.Offset(, 78) Sheets("JSA Procedure").Range("R68").Value = rngFound.Offset(, 79) Sheets("JSA Procedure").Range("V68").Value = rngFound.Offset(, 80) Sheets("JSA Procedure").Range("B70").Value = rngFound.Offset(, 81) Sheets("JSA Procedure").Range("F70").Value = rngFound.Offset(, 82) Sheets("JSA Procedure").Range("J70").Value = rngFound.Offset(, 83) Sheets("JSA Procedure").Range("O70").Value = rngFound.Offset(, 84) Sheets("JSA Procedure").Range("R70").Value = rngFound.Offset(, 85) Sheets("JSA Procedure").Range("V70").Value = rngFound.Offset(, 86) Sheets("JSA Procedure").Range("B72").Value = rngFound.Offset(, 87) Sheets("JSA Procedure").Range("F72").Value = rngFound.Offset(, 88) Sheets("JSA Procedure").Range("J72").Value = rngFound.Offset(, 89) Sheets("JSA Procedure").Range("O72").Value = rngFound.Offset(, 90) Sheets("JSA Procedure").Range("R72").Value = rngFound.Offset(, 91) Sheets("JSA Procedure").Range("V72").Value = rngFound.Offset(, 92) Sheets("JSA Procedure").Range("B74").Value = rngFound.Offset(, 93) Sheets("JSA Procedure").Range("F74").Value = rngFound.Offset(, 94) Sheets("JSA Procedure").Range("J74").Value = rngFound.Offset(, 95) Sheets("JSA Procedure").Range("O74").Value = rngFound.Offset(, 96) Sheets("JSA Procedure").Range("R74").Value = rngFound.Offset(, 97) Sheets("JSA Procedure").Range("V74").Value = rngFound.Offset(, 98) Sheets("JSA Procedure").Range("B76").Value = rngFound.Offset(, 99) Sheets("JSA Procedure").Range("F76").Value = rngFound.Offset(, 100) Sheets("JSA Procedure").Range("J76").Value = rngFound.Offset(, 101) Sheets("JSA Procedure").Range("O76").Value = rngFound.Offset(, 102) Sheets("JSA Procedure").Range("R76").Value = rngFound.Offset(, 103) Sheets("JSA Procedure").Range("V76").Value = rngFound.Offset(, 104) Sheets("JSA Procedure").Range("B78").Value = rngFound.Offset(, 105) Sheets("JSA Procedure").Range("F78").Value = rngFound.Offset(, 106) Sheets("JSA Procedure").Range("J78").Value = rngFound.Offset(, 107) Sheets("JSA Procedure").Range("O78").Value = rngFound.Offset(, 108) Sheets("JSA Procedure").Range("R78").Value = rngFound.Offset(, 109) Sheets("JSA Procedure").Range("V78").Value = rngFound.Offset(, 110) Sheets("JSA Procedure").Range("B80").Value = rngFound.Offset(, 111) Sheets("JSA Procedure").Range("F80").Value = rngFound.Offset(, 112) Sheets("JSA Procedure").Range("J80").Value = rngFound.Offset(, 113) Sheets("JSA Procedure").Range("O80").Value = rngFound.Offset(, 114) Sheets("JSA Procedure").Range("R80").Value = rngFound.Offset(, 115) Sheets("JSA Procedure").Range("V80").Value = rngFound.Offset(, 116) Sheets("JSA Procedure").Range("B82").Value = rngFound.Offset(, 117) Sheets("JSA Procedure").Range("F82").Value = rngFound.Offset(, 118) Sheets("JSA Procedure").Range("J82").Value = rngFound.Offset(, 119) Sheets("JSA Procedure").Range("O82").Value = rngFound.Offset(, 120) Sheets("JSA Procedure").Range("R82").Value = rngFound.Offset(, 121) Sheets("JSA Procedure").Range("V82").Value = rngFound.Offset(, 122) Sheets("JSA Procedure").Range("B84").Value = rngFound.Offset(, 123) Sheets("JSA Procedure").Range("F84").Value = rngFound.Offset(, 124) Sheets("JSA Procedure").Range("J84").Value = rngFound.Offset(, 125) Sheets("JSA Procedure").Range("O84").Value = rngFound.Offset(, 126) Sheets("JSA Procedure").Range("R84").Value = rngFound.Offset(, 127) Sheets("JSA Procedure").Range("V84").Value = rngFound.Offset(, 128) Sheets("JSA Procedure").Range("B86").Value = rngFound.Offset(, 129) Sheets("JSA Procedure").Range("F86").Value = rngFound.Offset(, 130) Sheets("JSA Procedure").Range("J86").Value = rngFound.Offset(, 131) Sheets("JSA Procedure").Range("O86").Value = rngFound.Offset(, 132) Sheets("JSA Procedure").Range("R86").Value = rngFound.Offset(, 133) Sheets("JSA Procedure").Range("V86").Value = rngFound.Offset(, 134) Sheets("JSA Procedure").Range("B88").Value = rngFound.Offset(, 135) Sheets("JSA Procedure").Range("F88").Value = rngFound.Offset(, 136) Sheets("JSA Procedure").Range("J88").Value = rngFound.Offset(, 137) Sheets("JSA Procedure").Range("O88").Value = rngFound.Offset(, 138) Sheets("JSA Procedure").Range("R88").Value = rngFound.Offset(, 139) Sheets("JSA Procedure").Range("V88").Value = rngFound.Offset(, 140) Sheets("JSA Procedure").Range("B90").Value = rngFound.Offset(, 141) Sheets("JSA Procedure").Range("F90").Value = rngFound.Offset(, 142) Sheets("JSA Procedure").Range("J90").Value = rngFound.Offset(, 143) Sheets("JSA Procedure").Range("O90").Value = rngFound.Offset(, 144) Sheets("JSA Procedure").Range("R90").Value = rngFound.Offset(, 145) Sheets("JSA Procedure").Range("V90").Value = rngFound.Offset(, 146) Sheets("JSA Procedure").Range("B92").Value = rngFound.Offset(, 147) Sheets("JSA Procedure").Range("F92").Value = rngFound.Offset(, 148) Sheets("JSA Procedure").Range("J92").Value = rngFound.Offset(, 149) Sheets("JSA Procedure").Range("O92").Value = rngFound.Offset(, 150) Sheets("JSA Procedure").Range("R92").Value = rngFound.Offset(, 151) Sheets("JSA Procedure").Range("V92").Value = rngFound.Offset(, 152) Sheets("JSA Procedure").Range("B94").Value = rngFound.Offset(, 153) Sheets("JSA Procedure").Range("F94").Value = rngFound.Offset(, 154) Sheets("JSA Procedure").Range("J94").Value = rngFound.Offset(, 155) Sheets("JSA Procedure").Range("O94").Value = rngFound.Offset(, 156) Sheets("JSA Procedure").Range("R94").Value = rngFound.Offset(, 157) Sheets("JSA Procedure").Range("V94").Value = rngFound.Offset(, 158) Sheets("JSA Procedure").Range("B96").Value = rngFound.Offset(, 159) Sheets("JSA Procedure").Range("F96").Value = rngFound.Offset(, 160) Sheets("JSA Procedure").Range("J96").Value = rngFound.Offset(, 161) Sheets("JSA Procedure").Range("O96").Value = rngFound.Offset(, 162) Sheets("JSA Procedure").Range("R96").Value = rngFound.Offset(, 163) Sheets("JSA Procedure").Range("V96").Value = rngFound.Offset(, 164) Sheets("JSA Procedure").Range("B98").Value = rngFound.Offset(, 165) Sheets("JSA Procedure").Range("F98").Value = rngFound.Offset(, 166) Sheets("JSA Procedure").Range("J98").Value = rngFound.Offset(, 167) Sheets("JSA Procedure").Range("O98").Value = rngFound.Offset(, 168) Sheets("JSA Procedure").Range("R98").Value = rngFound.Offset(, 169) Sheets("JSA Procedure").Range("V98").Value = rngFound.Offset(, 170) Sheets("JSA Procedure").Range("B100").Value = rngFound.Offset(, 171) Sheets("JSA Procedure").Range("F100").Value = rngFound.Offset(, 172) Sheets("JSA Procedure").Range("J100").Value = rngFound.Offset(, 173) Sheets("JSA Procedure").Range("O100").Value = rngFound.Offset(, 174) Sheets("JSA Procedure").Range("R100").Value = rngFound.Offset(, 175) Sheets("JSA Procedure").Range("V100").Value = rngFound.Offset(, 176) Sheets("JSA Procedure").Range("B102").Value = rngFound.Offset(, 177) Sheets("JSA Procedure").Range("F102").Value = rngFound.Offset(, 178) Sheets("JSA Procedure").Range("J102").Value = rngFound.Offset(, 179) Sheets("JSA Procedure").Range("O102").Value = rngFound.Offset(, 180) Sheets("JSA Procedure").Range("R102").Value = rngFound.Offset(, 181) Sheets("JSA Procedure").Range("V102").Value = rngFound.Offset(, 182) Sheets("JSA Procedure").Range("B104").Value = rngFound.Offset(, 183) Sheets("JSA Procedure").Range("F104").Value = rngFound.Offset(, 184) Sheets("JSA Procedure").Range("J104").Value = rngFound.Offset(, 185) Sheets("JSA Procedure").Range("O104").Value = rngFound.Offset(, 186) Sheets("JSA Procedure").Range("R104").Value = rngFound.Offset(, 187) Sheets("JSA Procedure").Range("V104").Value = rngFound.Offset(, 188) ' Put photos back if available ' Photo 1 If Sheets("JSA Procedure").Range("B20").Value < "" Then Sheets("JSA Procedure").Range("B16").Select Application.ScreenUpdating = False ActiveSheet.Unprotect Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim mypic As Picture Dim res As Variant Set WB = ActiveWorkbook res = rngFound.Offset(, 17).Value If res = False Then Exit Sub Set SH = Sheets("JSA Procedure") Set rng = Sheets("JSA Procedure").Range("B16") Set mypic = SH.Pictures.Insert(res) With mypic .Top = rng.Top .Left = rng.Left .Locked = False mypic.ShapeRange.LockAspectRatio = msoTrue mypic.ShapeRange.Height = 213.1 mypic.ShapeRange.Width = 275.1 mypic.ShapeRange.Rotation = 0# ActiveCell.Offset(4, 0).Value = res End With End If ' Photo 2 If Sheets("JSA Procedure").Range("O20").Value < "" Then Sheets("JSA Procedure").Range("O16").Select Application.ScreenUpdating = False ActiveSheet.Unprotect Set WB = ActiveWorkbook res = rngFound.Offset(, 18).Value If res = False Then Exit Sub Set SH = Sheets("JSA Procedure") Set rng = Sheets("JSA Procedure").Range("O16") Set mypic = SH.Pictures.Insert(res) With mypic .Top = rng.Top .Left = rng.Left .Locked = False mypic.ShapeRange.LockAspectRatio = msoTrue mypic.ShapeRange.Height = 213.1 mypic.ShapeRange.Width = 275.1 mypic.ShapeRange.Rotation = 0# ActiveCell.Offset(4, 0).Value = res End With End If End If End With End If Unload Me Sheets("Data").Visible = False Sheets("JSA Procedure").Select Sheets("JSA Procedure").PrintPreview ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Application.ScreenUpdating = True End Sub I need to apply a conditional FIND so that the value selected in LISTBOX3(column B) ALSO has the LISTBOX2 value in the same row (column A) How can i do this ? Corey.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost/help not finding the correct terminology questions for help | New Users to Excel | |||
Finding Correct Answer | Excel Worksheet Functions | |||
Please help with finding correct functions!!! | New Users to Excel | |||
conditional not finding correct results | Excel Programming | |||
Finding or creating correct formula | Excel Worksheet Functions |