Solved: Help!My code is find one value not all

[suny100]

here is my code to find data on other sheet "if it's founded on other sheet or not" my code here just find the first value not all other
Could anybody here help me??


Private Sub CommandButton8_Click()
Dim valFIND As Range
Dim iRow As Long
Dim sB As String

For iRow = 2 To 500
If Cells(iRow, "E") = "1" Then
sB = Cells(iRow, "B").Value
On Error Resume Next
Set valFIND = Sheet4.Cells.Find(sB, LookIn:=xlValues, LookAt:=xlWhole)
If Not valFIND Is Nothing Then
Sheet4.Activate
valFIND.Select
Exit Sub
End If
End If
Next iRow

MsgBox "Value " & sB & " was not found"
End Sub

[GTO]

Greetings and welcome to VBAX :-)

Let us say that cell E2 contains 1, and cell B2 contains 'Apple'. Now as I understand it, we will look for 'Apple' on Sheet4 ('Sheet4' being the CodeName).

Are you asking to find all cells on Sheet4, where the cell contains 'Apple'?

If so, what are we doing upon finding the cell(s)?

Mark

[suny100]

Greetings and welcome to VBAX :-)

Let us say that cell E2 contains 1, and cell B2 contains 'Apple'. Now as I understand it, we will look for 'Apple' on Sheet4 ('Sheet4' being the CodeName).

Are you asking to find all cells on Sheet4, where the cell contains 'Apple'?

If so, what are we doing upon finding the cell(s)?

Mark


thanks for your reply

what i men is that if cells E2 , E4, E7 contains 1 , then when search in sheet 4 will search for 1 cells (one after one)

[GTO]

Okay, I am still not understanding. If B2 contains 'Apple' and B4 contains 'Pear' (and E2 and E4 both contain 1), let us assume that Apple is listed twice on Sheet4. Are we trying to find both cells containing 'Apple', or just one. If just one, which one. Finally, once we find one or both, what then?

[suny100]

Okay, I am still not understanding. If B2 contains 'Apple' and B4 contains 'Pear' (and E2 and E4 both contain 1), let us assume that Apple is listed twice on Sheet4. Are we trying to find both cells containing 'Apple', or just one. If just one, which one. Finally, once we find one or both, what then?

Coulmn B contain unique data not repeated , so if as you said in your example B2 contain apple and B4 contain pear and both E2 and E4 contain 1 , I need it to search for apple then search for pear "may give me msg box for each find" if not found any of them not do any thing"

[GTO]

Try:

Option Explicit

Sub exa()
Dim rngLookFor As Range
Dim aryVals As Variant
Dim i As Long

With Sheet1
If RangeFound(Range(.Cells(2, 2), .Cells(.Rows.Count, 2))) Is Nothing Then Exit Sub
aryVals = Range(.Cells(2, 2), RangeFound(Range(.Cells(2, 2), .Cells(.Rows.Count, 2)))).Value
End With

With Sheet2
For i = 1 To UBound(aryVals, 1)
Set rngLookFor = Nothing
Set rngLookFor = RangeFound(.Cells, aryVals(i, 1), .Cells(.Cells.Count), , xlWhole)
If Not rngLookFor Is Nothing Then
MsgBox "I found " & aryVals(i, 1) & " in " & _
rngLookFor.Parent.Name & "!" & _
rngLookFor.Address(False, False), _
vbInformation, vbNullString
End If
Next
End With
End Sub

Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

[GTO]

Sorry, please note that I just used sheets' codenames of Sheet1 and Sheet2. Please change to suit.

[suny100]

Sorry, please note that I just used sheets' codenames of Sheet1 and Sheet2. Please change to suit.


I change it but it also not work , it give me an "Over flow" error msg

[GTO]

I have to take off. I would suggest attaching a sample workbook.

[suny100]

I have to take off. I would suggest attaching a sample workbook.


here is sample workbook

i want the button "check" on sheet 'reservation' to check the numbers on coulmn B if there are any of them in 'finished' sheet

[CharlesH]

HI,


This thread is basically a duplication from here.
http://www.excelforum.com/excel-programming/776399-want-to-know-if-cell-value-is-found-in-other-sheet.html
If you asked the question at another site please refer to it.

[Rayman]

i attached a new file, look if its work for you
Regards

[Rayman]

oops

[Rayman]

Cant attach the file , here is the new code:


Option Compare Text
Private Sub CommandButton9_Click()
Dim valFIND As Range
Dim iRow As Long
Dim sB As String
For iRow = 2 To 500
If Cells(iRow, "E") = "1" And Cells(iRow, "E") <> "" Then
If Cells(iRow, "B").Value <> "" Then
sB = Cells(iRow, "B").Value
End If
On Error Resume Next
Set valFIND = Sheet4.Cells.Find(sB, LookIn:=xlValues, LookAt:=xlPart)
If valFIND Like "*" & sB Then
Sheet4.Activate
valFIND.Select
If valFIND <> "*" & sB Then
MsgBox "Value " & sB & " was not found"
End If
End If
End If
Next iRow

End Sub

[suny100]

Of course iw works , really many and many thanks for your great effort and help

thanks alot