[已解决]请大侠帮忙---急！

linxiya1

哪位大侠能帮我改一下，我想要改成Sheet1-Sheet100同时运行这个宏，现在运行他只能对Sheet1进行比对。。。。。谢谢了！
Dim rng As Range
With Sheet1
For Each rng In Union(.Range("C6:C200"), .Range("C6:C200"))
  If Len(rng.Value) <> "追货" And rng.Value = "追货" Then MsgBox "帅哥有追货产品，请注意。。。。：）": Exit Sub
Next
End With
End Sub

最佳答案

月排行榜 / 总排行榜

mxg825

2011-12-13 10:58

1. 
   
2. Private Sub Workbook_Open()
   
3. Call MXG825
   
4. End Sub
   
5. 
   
6. Sub MXG825()
   
7. Dim rng As Range
   
8. Dim sht As Object
   
9. Dim ZH%, TY%
   
10. For Each sht In Sheets '循环全部工作表
    
11. With sht
    
12. For Each rng In .Range("C6:C" & .Range("C65536").End(xlUp).Row)
    
13.     If InStr(rng.Text, "追货") > 0 Then
    
14.        ZH = ZH + 1
    
15.     ElseIf InStr(rng.Text, "严重拖延") > 0 Then
    
16.        TY = TY + 1
    
17.     End If
    
18. Next
    
19. End With
    
20. Next
    
21. MsgBox "帅哥有追货产品" & ZH & "单，严重拖延产品" & TY & "单，请注意。。。。：）"
    
22. End Sub
    

复制代码

跳转到最佳答案楼层

mxg825

1. Sub MXG825()
   
2. Dim rng As Range
   
3. Dim sht As Object
   
4. For Each sht In Sheets '循环全部工作表
   
5. With sht
   
6. For Each rng In .Range("C6:C" & .Range("C65536").End(xlUp).Row)
   
7.   If InStr(rng.Text, "追货") > 0 Then MsgBox "帅哥有追货产品，请注意。。。。：）": Exit Sub
   
8. Next
   
9. End With
   
10. Next
    
11. End Sub

复制代码

你要用什么事件触发？
单个工作表。。还是循环 100个工作表？

linxiya1

只要打开工作薄就自动运行，对整个工作簿里的工作表进行对比然后提示。

linxiya1

Private Sub Workbook_Open()
Call Workbook_1
Call Workbook_2
End Sub

Private Sub Workbook_1()
Dim rng As Range
With Sheet1
For Each rng In Union(.Range("C6:C200"), .Range("C6:C200"))
  If Len(rng.Value) <> "追货" And rng.Value = "追货" Then MsgBox "帅哥有追货产品，请注意。。。。：）": Exit Sub
Next
End With
End Sub

Private Sub Workbook_2()
Dim rng As Range
With Sheet1
For Each rng In Union(.Range("C6:C200"), .Range("C6:C200"))
  If Len(rng.Value) <> "严重拖延" And rng.Value = "严重拖延" Then MsgBox "帅哥有严重拖延产品，请注意。。。。：）": Exit Sub
Next
End With
End Sub

非常感谢mxg825的帮助，我原来的请大侠帮我改一下。现在套你的只能对比一个如果有几个比对怎么改？

mxg825

1. 
   
2. Private Sub Workbook_Open()
   
3. Call MXG825
   
4. End Sub
   
5. 
   
6. Sub MXG825()
   
7. Dim rng As Range
   
8. Dim sht As Object
   
9. Dim ZH%, TY%
   
10. For Each sht In Sheets '循环全部工作表
    
11. With sht
    
12. For Each rng In .Range("C6:C" & .Range("C65536").End(xlUp).Row)
    
13.     If InStr(rng.Text, "追货") > 0 Then
    
14.        ZH = ZH + 1
    
15.     ElseIf InStr(rng.Text, "严重拖延") > 0 Then
    
16.        TY = TY + 1
    
17.     End If
    
18. Next
    
19. End With
    
20. Next
    
21. MsgBox "帅哥有追货产品" & ZH & "单，严重拖延产品" & TY & "单，请注意。。。。：）"
    
22. End Sub
    

复制代码

linxiya1

mxg825 大侠，因为“追货”,“严重拖延”状态不是同时出现，我希望提示能分开提示。。。

mxg825

试试 这个吧！ 提示有多少单。。

linxiya1

非常谢谢mxg825 哥哥，效果很明显，功能很强大，交个朋友吧。258549193扣扣