rngFound not finding correct value when more than one match

C

Corey

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....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update existing data or Insert new problem 3
Is there something wrong with my code? 5
Code does not seem to work, but it should. 2
Keep Original Sheets Font Format when Populating data into other sheet 3
Stop TextBox Clearing Cell 3
Insert image from Cell value 0
My on-going problems with dates 3
Assistance required 4

Top