Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    125
    Thanked 5 Times in 5 Posts
    I have a workbook that has several worksheets with the text "variance" in Column A & G. and the variance amount in the same rows as the text "variance" in Columns D & I. I need a macro that will show must which sheets as well as the column & row numbers have a variance value either greater than 0 or less than 0 i.e not equal to zero

    I have attempted to write the code, but cannot get it to work-see my code below

    Your assistance will be most appreciated



    Sub Variance_Message()

    Dim ws As Worksheet, r As Range, s As Range, msg As String, ff As String, gg As String

    For Each ws In Sheets
    Set r = ws.Columns("a").Find("Variance")
    Set s = ws.Columns("h").Find("Variance")
    If Not r Is Nothing Then
    ff = r.Address
    Do
    If (r.Offset(, 3).Value > 5) + (r.Offset(, 1).Value <> 0) Then
    msg = msg & ws.Name & r.Address(0, 0)
    If (s.Offset(, 3).Value > 5) + (r.Offset(, 1).Value <> 0) Then

    msg = msg & ws.Name & r.Address(0, 0)
    msg = msg & ws.Name & s.Address(0, 0)
    End If
    Set r = ws.Columns("a").FindNext(r)
    Set s = ws.Columns("h").FindNext(s)
    Loop Until ff = r.Address
    Loop Until gg = s.Address
    End If
    Next
    MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    I have a couple of questions about your explanation of the problem vs the code sample.

    1. It seems that you are checking column B for a value <> 0 and column D for a variance > 5?
    2. It also seems that you are checking column I for a value <> 0 and column K for a variance > 5?
    3. Do you really mean to be adding the results in your IF tests? I think the + should be replaced by AND.
    4. I'm also wondering about the 2 Find command being next to each other. Are the Variance being checked on a single row or can they be on different rows?

    It would really help if you could attach a workbook with some sample data so we can see what you are trying to accomplish.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    The following code code should have read

    If (r.Offset(, 3).Value <> 0) + (r.Offset(, 1).Value <> 0) Then
    msg = msg & ws.Name & r.Address(0, 0)
    If (s.Offset(, 3).Value <>0) + (r.Offset(, 1).Value <> 0) Then

    I have attached sample data. What I want is a message box that alerts me if there is a value that is less than or greater than zero in the row to the right of the text "variance"

    your assistance will be most appreciated

    Regards

    Howard

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Looks like you forgot to attach the sample data.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    My apologies, I forgot to attach the workbook sample

    Regards

    Howard
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Ok, here's a possible solution. I've made some changes so you'll have to copy the macro out of the attached workbook and into your production workbook.

    I reformatted the East sheet so that all the branches lined up across the page where they were paired. I also checked all the summations, some had the wrong references after I lined things up. I also formatted the message for easier reading using the vbcrlf constant. I changed the name of the workbook so it won't overwrite your copy when you download it.

    I hope this solves your problem.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Thanks very much for all the help. Code works perfectly

    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
  •