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

    2 condition not.. (2000 sr 1)

    I have this macro i would want to insert into condition of copy:

    And Not .Cells(i, "H").Value = "ESTINTO" And Not .Cells(i, "H").Value = ""

    how to make it?

    Sub COPIA_PERDITE()
    'COPIA INTERA RIGA CON SELEZIONE DELLE COLONNE
    Worksheets("PERDITE").Range("A3:G2500").ClearConte nts

    Dim i As Long, LastRow As Long, PasteRng As Range
    LastRow = Sheets("SALDI").Range("A65536").End(xlUp).Row
    If Sheets("PERDITE").Range("A3").Value = "" Then
    Set PasteRng = Sheets("PERDITE").Range("A3")
    Else
    Set PasteRng = Sheets("PERDITE").Range("A65536").End(xlUp).Offset (1, 0)
    End If

    For i = 3 To LastRow
    With Sheets("SALDI")
    If .Cells(i, "E").Value = "00" And Not .Cells(i, "H").Value = "ESTINTO" And Not .Cells(i, "H").Value = "" Then
    Range(.Cells(i, "A"), .Cells(i, "G")).Copy Destination:=PasteRng
    Set PasteRng = PasteRng.Offset(1, 0)
    End If
    End With
    Next i

    Call TOTALI_PERDITE
    End Sub

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

    Re: 2 condition not.. (2000 sr 1)

    FIRED!!!

    The condition are into column J of SALDI...

    Original macro:

    Sub COPIA_PERDITE()
    'COPIA INTERA RIGA CON SELEZIONE DELLE COLONNE
    Worksheets("PERDITE").Range("A3:G2500").ClearConte nts

    Dim i As Long, LastRow As Long, PasteRng As Range
    LastRow = Sheets("SALDI").Range("A65536").End(xlUp).Row
    If Sheets("PERDITE").Range("A3").Value = "" Then
    Set PasteRng = Sheets("PERDITE").Range("A3")
    Else
    Set PasteRng = Sheets("PERDITE").Range("A65536").End(xlUp).Offset (1, 0)
    End If

    For i = 3 To LastRow
    With Sheets("SALDI")
    If .Cells(i, "E").Value = "00" Then
    Range(.Cells(i, "A"), .Cells(i, "G")).Copy Destination:=PasteRng
    Set PasteRng = PasteRng.Offset(1, 0)
    End If
    End With
    Next i

    'Call TOTALI_PERDITE
    End Sub

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

    Re: 2 condition not.. (2000 sr 1)

    Please read my previous reply again.

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

    Re: 2 condition not.. (2000 sr 1)

    Hans, sorry, i have solved ... with a little attention on your last post.
    Tks.

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

    Re: 2 condition not.. (2000 sr 1)

    Cells has two number arguments: the row number and the column number (not letter). For example, cell B5 is Cells(5, 2) because it is in row 5, column 2. So you cannot use .Cells(i, "E") since "E" is a string, not a number. You must use .Cells(i, 5) instead, since "E" is the 5th column. You must change the others the same way.

    Added later: you learn something new every day. To my surprise, the syntax Cells(5, "B") actually works the same as Cells(5, 2). I always thought you had to supply to numeric arguments.

Posting Permissions

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