my code is a problem with message box

0

hello

I try   writing   code    , actually  it  works  but  not   completely   when     write  the  value  in     e1   it  shows  the  message    what  I  would   when   I  write   the  right  value   shouldn't show  the  message   and  if   the   value    is  not  existed   then  shows message  and  clear   the  cells 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim ws As Worksheet
Dim lr As Long, res As Variant
For Each ws In Worksheets
lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
res = Application.Match(Target.Value, ws.Range("B1:B" & lr), 0)
If IsError(res) Then MsgBox "No match found": Exit Sub
Target.Range("E3:E7").ClearContents
With Target
 .Offset(2, 0).Value = ws.Cells(res, 2).Value
 .Offset(3, 0).Value = ws.Cells(res, 3).Value
 .Offset(4, 0).Value = ws.Cells(res, 4).Value
 .Offset(5, 0).Value = ws.Cells(res, 5).Value
 .Offset(6, 0).Value = ws.Cells(res, 6).Value
End With
Next
End Sub

Answer
Discuss

Answers

0
Selected Answer

Hi Speed

Please try the attached file- it should work for you. The modified code is below and the changes I made are in bold.

Your message box would always appear since only one sheet has the target value (so another your trigger the "Not found" and exit the sub. Instead I've acted on the absence of an error (Not IsError(res)) - in this case the flag Fnd is set to true abd the OUTPUT sheet is populated.

I also added a test so you didn't search unnecessarily for values in OUTPUT (though the vlaues would be found!) and pushed the "Not found" message to the end (if Fnd = False)

REVISION 1: Moved the line Range("E3:E7").ClearContents to after the DIM statements- your code had it happening only if a part was found and you hadTarget. in front of it (an error). Revised file attached.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Dim ws As Worksheet
Dim lr As Long, res As Variant, Fnd As Boolean
Range("E3:E7").ClearContents
For Each ws In Worksheets
If Not ws.Name = Target.Parent.Name Then ' don't look for values on the target sheet
    lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
    res = Application.Match(Target.Value, ws.Range("B1:B" & lr), 0)
    If Not IsError(res) Then 'only do the following if E1 contents are on this worksheet
        Fnd = True 'set a flag to say it was found
        With Target
         .Offset(2, 0).Value = ws.Cells(res, 2).Value
         .Offset(3, 0).Value = ws.Cells(res, 3).Value
         .Offset(4, 0).Value = ws.Cells(res, 4).Value
         .Offset(5, 0).Value = ws.Cells(res, 5).Value
         .Offset(6, 0).Value = ws.Cells(res, 6).Value
        End With
    End If
End If
Next ws
If Fnd = False Then MsgBox Target.Value & "- Code not found"
End Sub

Hope this fixes your problem.

Discuss

Discussion

thanks  john    but  the   code  is still  a proplem      it  needs  some  fixing     when  i  write    a new  value   is  not  existed    yes   the  messege  shows  , but  does not  clear  range  e3:e7      and  if    the   e2  is  empty    and  press enter   it  shows  the  message   it  shouldn't  show  the  message  
briefly 
  1 -   if  the  value   is  wrong   then  show  the  message   and   clears content   from e3:e7
2-   if    the   e2  is  empty   it should  clear  from e2: e7   without  showing  message  thanks  for  your  co-oporate
speed (rep: 12) Jan 21, '21 at 3:41 pm
Oops! Will correct and revise Answer. 
John_Ru (rep: 772) Jan 21, '21 at 3:45 pm
Done (it was a very easy change- see Revision 1 and new file)
John_Ru (rep: 772) Jan 21, '21 at 3:59 pm
I leave you to corrrect your point 2. Clue -make the MsgBox conditional on both Fnd and the value of E1
John_Ru (rep: 772) Jan 21, '21 at 4:01 pm
thanks  john  for  your  assistance     just   I  would  consult  about  point  2    i  added    this   line   before  the  last  line   and  it  works,so  am i  right   or  wrong ?
If Target.Value = "" Then Exit Sub
speed (rep: 12) Jan 21, '21 at 4:24 pm
Thanks for selecting my answer. 

Your addition works so it's okay. You could however put it near the start, after the Range clear statement (and save time looping through sheets) 
John_Ru (rep: 772) Jan 21, '21 at 5:05 pm
many thanks  for  guide  me  the  right  way  to  work  the  code 
speed (rep: 12) Jan 22, '21 at 5:34 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login