Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Macro to show message when there is a variance in

    I have a spreadsheet, which contains the word variance in column A and a value in column D that is in line with the word "variance" in col A.

    I have written VBA code that will advise me where there is a value that is either greater or less that zero in column D that is in line with the word "variance", but cannot get it to work properly. I have also attached sample data below. It would be appreciated if you could assist me

    Sub Variance_Message()
    Sheets("sheet1").Select
    Dim ws As Worksheet, r As Range, msg As String, ff As String
    For Each ws In Sheets
    Set r = ws.Columns("b").Find("Variance")
    If Not r Is Nothing Then
    ff = r.Address
    Do
    If (r.Offset(, 3).Value < 0) + (r.Offset(, 3).Value > 0) Then
    msg = msg & ws.Name & r.Address(0, 0)
    End If
    Set r = ws.Columns("b").FindNext(r)
    Loop Until ff = r.Address
    End If
    Next
    MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")
    End Sub
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    You need to start by looking for "Variance" in column A, not column B.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Martin

    Thanks for pointing me in the right direction. I have amended the code to look at column A, but the macro message is not giving me the correct row numbers. It would be appreciated if you would test the macro and amend the code

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    When I change the macro to search COl A the results I get are:
    Nissan A16
    Sheet1 A13
    Sheet1 A22
    Sheet1 A41
    Sheet1 A62
    Sheet1 A71
    Sheet1 A107
    Sheet1 A117

    These seem right to me. What results do you want to get?

    Note: for readibility I changed your line to:
    msg = msg & vbCrLf & ws.Name & vbTab & r.Address(0, 0)
    to add a carrigage / line-feed before each find and to add a tab between the sheet and the address
    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help. The code is working almost perfectly,it is showing the variances in the applicable rows.except Nissan A16. There is no variance in A16, so why would this appear. Please test & correct

    Regards

    Howard

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Explain the logic of why it has no variance. There is the word "variance" in A16 and there is a non-zero value in D16. Those are what you define in your code as a "Variance"....

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    My apologies and many thanks for the help. I did not see the sheet Nissan which should have been deleted.

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •