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

    Particular condition.... (2000)

    In his wbook are TABELLA and T0018 sheets.

    In TABELLA column O are a list of value
    In TABELLA column P2 and Q2 a dates

    How to loop line by line the row in T0018 only if the date in H are between P2 and Q2 from TABELLA first condition
    second condition, and, one or more value from list in O TABELLA are in column B of T0018

    Hope i am clear...

  2. #2
    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

    Re: Particular condition.... (2000)

    How about this? You didn't say what you wanted to do with info so I just colored the cells in col B...

    Steve

    <pre>Option Explicit
    Sub Test()
    Dim rSett As Range
    Dim rSelez As Range
    Dim dStart As Date
    Dim dEnd As Date
    Dim iOffset As Integer
    Dim rCell As Range
    Dim x As Long

    iOffset = 6 ' Col B to H
    With Worksheets("TABELLA")
    dStart = .Range("P2")
    dEnd = .Range("Q2")
    Set rSelez = .Range(.Cells(2, 15), _
    .Cells(.Rows.Count, 15).End(xlUp))
    End With

    With Worksheets("T0018")
    Set rSett = .Range(.Cells(3, 2), _
    .Cells(.Rows.Count, 2).End(xlUp))
    End With
    For Each rCell In rSett
    x = 0
    On Error Resume Next
    x = Application.WorksheetFunction.Match(rCell.Value, rSelez, 0)
    On Error GoTo 0
    If x <> 0 Then
    If rCell.Offset(0, iOffset) > dStart And _
    rCell.Offset(0, iOffset) < dEnd Then
    rCell.Interior.Color = vbYellow
    End If
    End If
    Next
    MsgBox "Done"
    Set rCell = Nothing
    Set rSett = Nothing
    Set rSelez = Nothing
    End Sub</pre>


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

    Re: Particular condition.... (2000)

    What should the result be?

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

    Re: Particular condition.... (2000)

    Hi Steve the code work very fine....

    Little modifiy in:
    If rCell.Offset(0, iOffset) > dStart And rCell.Offset(0, iOffset) < dEnd Then
    to
    If rCell.Offset(0, iOffset) => dStart And rCell.Offset(0, iOffset) <= dEnd Then

    tks.

  5. #5
    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

    Re: Particular condition.... (2000)

    I took "between" to mean not including the borders <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Steve

Posting Permissions

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