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

Hello.

Try...

..... ElseIf (LCase(Sheet3.Cells(a, 4)) <> moo And _     LCase(Sheet2.Cells(a, 4)) <> moo) Then     MsgBox "Invalid"     Exit Sub End If

..and let us know.

____________________________
Real Programmer can count up to 1024 on his fingers

thanx a million!! actually i sought of tried that before but my mistake was using else and i think i didnt add the exit sub...anyway thanx again!!

I just cant believe this, Ironically its not working again,....everything is correct but when i tried to run it again today, the message box appears again even when the data is valid....ill paste the coding here and maybe someone could tell me whats the error here....thanx a llot...

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

b = 1
c = 1
d = 1

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

For a = 8 To 200

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(Sheet12.Cells(a, 4)) = moo Then
  
        Sheet5.Cells(d + 7, 1) = Sheet2.Cells(a, 2)
        Sheet5.Cells(d + 7, 2) = Sheet2.Cells(a, 5)
        Sheet5.Cells(d + 7, 3) = Sheet2.Cells(a, 9)
        Sheet5.Cells(d + 7, 4) = Sheet2.Cells(a, 18)
        Sheet5.Cells(d + 7, 5) = Sheet2.Cells(a, 25)
        Sheet5.Cells(d + 7, 6) = Sheet2.Cells(a, 6)
        d = d + 1
  
ElseIf (LCase(Sheet3.Cells(a, 4)) <> moo And LCase(Sheet2.Cells(a, 4)) <> moo And LCase(Sheet12.Cells(a, 4)) <> moo) Then
    MsgBox "Sorry, This Data Could Not Be Found"
    Exit Sub
End If
Next a


End Sub