i need to add a message box stating that the data is not available when Sheet2.cells(i,2)<>program...how do i insert this code..and generate a message box because i tried but im using the For loop and the message box keeps on looping...please help...

moo = Sheet1.TextBox1.Text
lead = Sheet1.TextBox2.Text
program = (moo) & (lead) & (tape)

For i = 1 To 500
    For j = 1 To 20

If Sheet2.Cells(i, 2) = program Then
        If Sheet1.Cells(21, j) = "" Then
            Sheet1.Cells(21, j) = Sheet2.Cells(i, j)
        
             For l = 1 To 20
                If Sheet1.Cells(21, l) = "Y" Then
                    Sheet1.Cells(20, l) = Sheet2.Cells(2, l)
                    Sheet1.Cells(20, l).Font.Color = vbBlue
                End If
        Next l
        End If

  End If
    Next j
Next i



End Sub


[Edited by mayanair on 09-05-2003 at 10:12 AM GMT]

How about


dim no_data as boolean

no_data = false

moo = Sheet1.TextBox1.Text
lead = Sheet1.TextBox2.Text
program = (moo) & (lead) & (tape)

For i = 1 To 500
    For j = 1 To 20

        If Sheet2.Cells(i, 2) = program Then
            If Sheet1.Cells(21, j) = "" Then
                 Sheet1.Cells(21, j) = Sheet2.Cells(i, j)
        
                 For l = 1 To 20
                     If Sheet1.Cells(21, l) = "Y" Then
                         Sheet1.Cells(20, l) = Sheet2.Cells(2, l)
                         Sheet1.Cells(20, l).Font.Color = vbBlue
                     End If
                Next l
             endif
        else
            msgbox "No Data"
            'set the flag that no data is available
            no_data = true
        End If

        ' exit for next loop if flag set
        if no_data = true then
            exit for
        endif
    Next j

    ' exit for next loop if flag set
    if no_data = true then
        exit for
    endif
Next i




Cheers Steve

tried ths...but it still doesnt work...the coding jumps to the else statement because it reads for i=1 so since the data in the first row is not the same with what the user has typed in the textbox it goes to else instead....so the message box appear even if the data is available in another row....any other ideas...thanx a lot...i need some sort of a search algorithm....coz the main purpose here is to search the avilability of a unique name and then display the rest of the columns

[Edited by mayanair on 09-05-2003 at 05:21 PM GMT]

Dim no_data As Boolean

no_data = True

moo = Sheet1.TextBox1.Text
lead = Sheet1.TextBox2.Text
program = (moo) & (lead) & (tape)

For i = 1 To 500
    If sheet2.cells(i, 2) = program Then
        no_data = False
        Exit For
    End If
Next i

If no_data = True Then
    MsgBox "No Data"
Else
    For i = 1 To 500
        
        For j = 1 To 20
  
            If sheet2.cells(i, 2) = program Then
                If Sheet1.cells(21, j) = "" Then
                     Sheet1.cells(21, j) = sheet2.cells(i, j)
            
                     For l = 1 To 20
                         If Sheet1.cells(21, l) = "Y" Then
                             Sheet1.cells(20, l) = sheet2.cells(2, l)
                             Sheet1.cells(20, l).Font.Color = vbBlue
                         End If
                    Next l
                 End If
            End If
        Next j
    Next i
End If

I have a feeling you could get the FIND method to do this search much quicker and easier...these tid-bits may help you...

Determines Last Row used in a Sheet

LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

Searches a Range for a Specified Value (2) And Replaces With Another Value (5) - Use the FIND Method

With Worksheets(1).Range("a1:a" & LastRow)     Set c = .Find(2, lookin:=xlValues)     If Not c Is Nothing Then         firstAddress = c.Address         Do             c.Value = 5             Set c = .FindNext(c)         Loop While Not c Is Nothing And c.Address <> firstAddress     End If End With

____________________________
Eggheads unite! You have nothing to lose but your yolks.

Nice  

Bean, could u explain to me how ths code works...and how can i apply it to the code that ive already done...thanx.

i am not sure if I understand exactly what your purpose is, but this will search a specified range (B1 to last row) for a specified value ("JOE") and display a message based on whether that value was found...

'Snippet (no declarations made) no_data = False look4 = "JOE" LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count With Worksheets(1).Range("B1:B" & LastRow)     Set c = .Find(look4,LookIn:=xlValues)     If Not c Is Nothing Then         no_data = True     End If End With If no_data = False Then     MsgBox "Sorry, Value Was NOT Found" ElseIf no_data = True Then     MsgBox "Woo-Hoo! Value Was Found!" End If

____________________________
Eggheads unite! You have nothing to lose but your yolks.