Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    why the not work the dupes controll... (2000 sr 1)

    i have used a old piece of code to controll dupes and not import if into column L already existis the index but not work.. why?

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

    Re: why the not work the dupes controll... (2000 sr 1)

    The variable INDEX is a string variable, and column L also contains string values. But in the line

    Set Found_INDEX = Foglio13.Columns("L:L").Find(Val(INDEX), lookat:=xlWhole)

    you look at the numeric value of INDEX. You shouldn't use Val here.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the not work the dupes controll... (2000 sr 1)

    ok now work well...
    Only a litle update...
    If you see into column J of this wbook are list and from this the user can choice a value, is possible insert automticly into refered cell of column I the date (into same format alreday existis) of current date? Naturally only if a cell is filled with the list from covalidate, if a user insert aother value appear a msg box "value not is present into list"....
    Hope i am clear...

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

    Re: why the not work the dupes controll... (2000 sr 1)

    Put the following code in the worksheet module of the A.T.CAMPANIA sheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("J2:J65536")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("J2:J65536")).Cells
    oCell.Offset(0, -1) = Date
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the not work the dupes controll... (2000 sr 1)

    your code work perfect ... but why if i canvel avalue into column J the refred date into column I not is cleared?
    in effect if i cancell in J456 cancell the the date in I456...

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

    Re: why the not work the dupes controll... (2000 sr 1)

    You could change the middle part to

    For Each oCell In Intersect(Target, Range("J2:J65536")).Cells
    If oCell = "" Then
    oCell.Offset(0, -1).ClearContents
    Else
    oCell.Offset(0, -1) = Date
    End If
    Next oCell

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the not work the dupes controll... (2000 sr 1)

    tks...

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the not work the dupes controll... (2000 sr 1)

    Hi Hans good mornig and nice saturday...

    Bsed on this file attachd into first post, is spossible to:

    If the user select from list in column M RECUPERO TOTALE and into column J (same line) is present PRATICA LEGALE show msg box "Bsed your selection not posiible to select RECUPERO TOTALE, choice other selection"....

    If you make example on this i modify, based your suggestion i insert the other condition...
    Tks.

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

    Re: why the not work the dupes controll... (2000 sr 1)

    RECUPERO TOTALE is not in the list for column M, at least not in the version you attached to the first post of this thread.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the not work the dupes controll... (2000 sr 1)

    Sorry, RECUPERO RATEALE

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

    Re: why the not work the dupes controll... (2000 sr 1)

    And I assume you meant PRATICA A LEGALE instead of PRATICA LEGALE. Try this code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("J2:J65536")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("J2:J65536")).Cells
    If oCell = "" Then
    oCell.Offset(0, -1).ClearContents
    Else
    oCell.Offset(0, -1) = Date
    End If
    Next oCell
    End If
    If Not Intersect(Target, Range("M2:M65536")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("M2:M65536")).Cells
    If oCell = "RECUPERO RATEALE" And _
    oCell.Offset(0, -3) = "PRATICA A LEGALE" Then
    MsgBox "You can't select RECUPERO RATEALE here!", vbExclamation
    oCell.ClearContents
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the not work the dupes controll... (2000 sr 1)

    perfect! if i have understand i can use the repeat of this block for other condition...

    .........
    If Not Intersect(Target, Range("M2:M65536")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("M2:M65536")).Cells
    If oCell = "RECUPERO RATEALE" And _
    oCell.Offset(0, -3) = "PRATICA A LEGALE" Then
    MsgBox "You can't select RECUPERO RATEALE here!", vbExclamation
    oCell.ClearContents
    End If
    Next oCell
    Application.EnableEvents = True
    End If

    .........

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

    Re: why the not work the dupes controll... (2000 sr 1)

    Yes.

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: why the not work the dupes controll... (2000 sr 1)

    Hans.... pearphs is mine Excel....Boh!
    You have maked for me (tks you;-)) this macro (se attached txt code)and i have insert into sheet code work fine...
    But for each changement on this sheet (when i test other macro, form example, when i insert a value from imported txt int a column A or B or other column ) the macro is actived...

    For example when i test this with PF8, and when i write this value with
    ..........

    Set ELENCO = Worksheets("A.T.CAMPANIA")


    ELENCO.Range("A" & Trim(Str(CONT))).Value = var_DIP
    ELENCO.Range("B" & Trim(Str(CONT))).Value = var_SEDE
    ELENCO.Range("C" & Trim(Str(CONT))).Value = Format((var_CAT), "#000")
    ...........

    the curso go into cope sheet of A.T. CAMPANIA ...

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

    Re: why the not work the dupes controll... (2000 sr 1)

    The Worksheet_Change event procedure will run each time a cell is modified, but it should only do something if a cell in column J or M is modified.

    I don't know what "the curso go into cope sheet of A.T. CAMPANIA" means.

Page 1 of 2 12 LastLast

Posting Permissions

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