0

I'm trying to run a macro that will first search for the cell and check the value next to the cell and if the cell = 0, get message-box "no differences" if the cell <>0 go to sheet. I want to do this for different sheets in the same workbook.

Sub Check()
'To check unaccounted difference in all sheets
Sheets("443000-jan").Select
    Cells.Find(what:="Unaccounted Diff").Select
    ActiveCell.Select
    ActiveCell.Offset(0, 1).Select
        If ActiveCell.Value = 0 Then
        MsgBox "No Differences Found"
       ElseIf ActiveCell.Values <> 0 Then
        Worksheets("443000-Jan").Visible = True
        Worksheets("443000-Jan").Activate
  End If
   Sheets("443001-SC").Select
    Cells.Find(what:="Unaccounted Diff").Select
    ActiveCell.Select
    ActiveCell.Offset(0, 1).Select
        If ActiveCell.Value = 0 Then
        MsgBox "No Differences Found"
       ElseIf ActiveCell.Values <> 0 Then
        Worksheets("443001-SC").Visible = True
        Worksheets("443001-SC").Activate

    End If

 End Sub
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
HRod
  • 11
  • 1
  • 1
    If I can offer some advice, reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) will make this a lot cleaner. – BigBen May 10 '19 at 18:54
  • Thanks will check that out. – HRod May 10 '19 at 18:55

1 Answers1

0

Assuming that you want to check that for all the sheets in a workbook you need to loop through. You will need to look at not using the select all over the place. I think it would be better if you assign the .find value to a variable and use that in your code.

Sub LoopSheets()
Dim WS as Worksheet

  For Each WS In ActiveWorkbook.Worksheets
  If Worksheets(WS).Name <> “Master” Then
     Cells.Find(what:="Unaccounted Diff").Select
         ActiveCell.Offset(0, 1).Select
         If ActiveCell.Value = 0 Then
            MsgBox "No Differences Found"
        ElseIf ActiveCell.Values <> 0 Then
            WS.Visible = True
            WS.Activate
        End If     
   End If
  Next WS
End Sub
QuickSilver
  • 730
  • 5
  • 28
  • Thank you for your answer, i guess I forgot to mention I have a master sheet I don't need to search for. – HRod May 13 '19 at 14:50
  • @HRod I’ve updated the answer to reflect the change. – QuickSilver May 13 '19 at 14:55
  • I'm definitely getting the message box but there are a few of the sheets that should come back as false and where the worksheet should activate. – HRod May 13 '19 at 15:34
  • @HRod sorry I do not understand what you mean. Can you explain what is the problem at what line you are getting different result than expected. You can go vba and excel side by side and run the code line by line and see where the problem occurs. – QuickSilver May 13 '19 at 16:18