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-prog...her-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:


[vba]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[/vba]

[suny100]

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

thanks alot