Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Active row - column G & H - call macro if unmatch (excel - 2000)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help> for instructions.)

    Hello everyone, new to VBA, been picking up some here and there, but seem to stumble on the easy stuff. I need a worksheet change event that monitors the active row, and compares between columns G & H. If not matched, call macro. I have a more thorough post here
    http://www.excelforum.com/showthread...170#post823170
    to explain in more detail if necessary. Thanks for any help. Jeta1

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active row - column G & H - call macro if unmatch (excel - 2000)

    Welcome to Woody's Lounge.

    From your post on ExcelForum, I gather that column H is populated by a bar code scanner. I don't know if this triggers the Worksheet_Change event; I don't work with bar code scanners. If the event is triggered, you should be able to use code like this:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    ' Test if there are target cells in column H
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
    ' Loop through target cells in column H
    For Each oCell In Intersect(Target, Range("H:H")).Cells
    ' Test if cell and its neighbor in column H are filled
    If Not (oCell.Value = "" Or oCell.Offset(0, -1).Value = "") Then
    ' Test if values different
    If Not (oCell.Value = oCell.Offset(0, -1).Value) Then
    ' Action!
    Call Oodles
    Debug.Print oCell.Address
    End If
    End If
    Next oCell
    End If
    Set oCell = Nothing
    End Sub

  3. #3
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active row - column G & H - call macro if unmatch (excel - 2000)

    Thanks Hans, the bar scan will trigger an event, and this looks like it will do what I want, with the exception, that I need it to only monitor the active row. When there is a mismatch, I have it storing the data in a locked cell, so when it does the compare, it always sees the mismatch, and fires the macro. Also in row 1, I have header information (ie. Part Label, Container Label) so I think it see that mismatch also, and fires the macro.
    Thanks again. Jeta1

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active row - column G & H - call macro if unmatch (excel - 2000)

    The Target argument passed by the Worksheet_Change event represents the cell or cells that have changed, usually only one cell. By taking the intersection of the Target and column H, you get only those cells in column H that have changed. The header in row 1 won't change, I assume, so it will not be part of the target.

  5. #5
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active row - column G & H - call macro if unmatch (excel - 2000)

    Thanks again Hans, it seems to fire the macro every time, even when they match. I wonder if it's because excel thinks that the bar code scanner is pasting the value. I understand that events can not be triggered by a paste. Wonder if I should try to turn the event update off, and then on again?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active row - column G & H - call macro if unmatch (excel - 2000)

    It's worth a try. As I wrote before, I don't use a bar code scanner myself, so I can't perform realistic tests.

  7. #7
    New Lounger
    Join Date
    Dec 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active row - column G & H - call macro if unmatch (excel - 2000)

    Well Thanks anyway Hans, I can not get it to work, I even tried to copy the columns over using " Range("H1:H500").Value = Range("G1:G500").Value ", and that doesn't work either.. I have got 3 days in it, just trying to work this out. If anyone else has any ideas that may help me out, I would really appreciate it.
    Thanks again. Jeta1

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Active row - column G & H - call macro if unmatch (excel - 2000)

    To copy G1:G500 to H1:H500 in code, use

    Range("G1:G500").Copy Destination:=Range("H1:H500")

    or else

    Dim i As Long
    For i = 1 To 500
    Range("H" & i).Value = Range("G" & i).Value
    Next i

Posting Permissions

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