could u please help me with ths coding here...i seem to get it rite but the logic of where to put the message box is totally wrong...it pops out even when the data is available in the worksheet......thanx


Private Sub CommandButton1_Click()
moo = LCase(Sheet5.TextBox1.Text)

b = 1
c = 1

If Sheet5.TextBox1.Text = "" Then
MsgBox "Please Key In a Value"
Exit Sub
End If

For a = 9 To 20

If LCase(Sheet3.Cells(a, 4)) = moo Then
        
      
        Sheet5.Cells(b + 7, 1) = Sheet3.Cells(a, 2)
        Sheet5.Cells(b + 7, 2) = Sheet3.Cells(a, 3)
        Sheet5.Cells(b + 7, 3) = Sheet3.Cells(a, 10)
        Sheet5.Cells(b + 7, 4) = Sheet3.Cells(a, 14)
        Sheet5.Cells(b + 7, 5) = Sheet3.Cells(a, 24)
        Sheet5.Cells(b + 7, 6) = Sheet3.Cells(a, 5)
        b = b + 1
      
    
    ElseIf LCase(Sheet2.Cells(a, 4)) = moo Then
        
        Sheet5.Cells(c + 7, 1) = Sheet2.Cells(a, 2)
        Sheet5.Cells(c + 7, 2) = Sheet2.Cells(a, 5)
        Sheet5.Cells(c + 7, 3) = Sheet2.Cells(a, 9)
        Sheet5.Cells(c + 7, 4) = Sheet2.Cells(a, 18)
        Sheet5.Cells(c + 7, 5) = Sheet2.Cells(a, 25)
        Sheet5.Cells(c + 7, 6) = Sheet2.Cells(a, 6)
        c = c + 1
    
    ElseIf (LCase(Sheet3.Cells(a, 4)) <> moo And LCase(Sheet2.Cells(a, 4)) <> moo) Then
    MsgBox "Invalid"
    End If
      
Next a

End Sub  

Are you referrring to your first MsgBox the one inside the initial test for "moo"? The textbox shouldn't be doing this unless it isn't really empty (""?)...

Maybe try this...

... ... moo = LCase(Sheet5.TextBox1.Text) b = 1 c = 1 If Len(moo) = 0 Then    MsgBox "Please Key In a Value"    Exit Sub End If ... ...

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

im talking about the second message box...the first one works fine

Check your data cause the If..Then looks OK...rather, it seems to work correctly for how its coded.

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

Check my reply at

http://www.andreavb.com/forum/viewtopic.php?TopicID=1463

  

____________________________
Real Programmer can count up to 1024 on his fingers