Martin Fishlock
12/13/2006 4:07:00 AM
There were a few errors in the earlier post try this one, check the vlookup
function maybe it works otherwise modify it see comments.:
Option Explicit
Sub FIND()
Dim RIGA As String
Dim RIGA_S As Long
Dim C As Variant
Dim wsH7469 As Worksheet
Dim wsStorico As Worksheet
Dim index
Application.ScreenUpdating = False
Set wsH7469 = Sheets("H7469")
Set wsStorico = Sheets("H7469_STORICO")
RIGA = 3
RIGA_S = wsStorico.Cells(65536, 1).End(xlUp).Row + 1
While Not wsH7469.Range("A" & RIGA) = ""
index = wsH7469.Range("R" & RIGA)
' if application.vlookup not works try
' Application.WorksheetFunction.VLookup
C = Application.VLookup( _
index, wsStorico.Range("R:R"), 1, False)
If (IsError(C)) Then
'COPY IF NOT FOUND IN wsStorico
wsH7469.Range("A" & RIGA & ":R" & RIGA).Copy
wsStorico.Range("A" & RIGA_S & ":R" & RIGA_S).PasteSpecial
xlPasteValues
RIGA_S = RIGA_S + 1
End If
RIGA = RIGA + 1
Wend
Application.ScreenUpdating = True
End Sub
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.
"Martin Fishlock" wrote:
> Have a try at this
>
> Sub FIND()
>
> Dim RIGA As String
> Dim RIGA_S As Long
> Dim C As Range
> Dim wsH7469 As Worksheet
> Dim wsStorico As Worksheet
> Dim index
> Application.ScreenUpdating = False
>
> Set wsH7469 = Sheets("H7469")
> Set wsStorico = Sheets("H7469_STORICO")
>
> RIGA = 3
> RIGA_S = wsStorico.Cells(65536, 1).End(xlUp).Row + 1
>
> While Not wsH7469.Range("A" & RIGA) = ""
> index = wsH7469.Range("R" & RIGA)
> C = Application.WorksheetFunction.VLookup( _
> index, wsStorico.Columns("R:R"), 1, False)
> If IsNA(C) Then
> 'COPY IF NOT FOUND IN wsStorico
> wsH7469.Range("A" & RIGA & ":R" & RIGA).Copy
> wsStorico.PasteSpecial xlPasteValues, wsStorico.Range("A" &
> RIGA_S & ":R" & RIGA_S)
> RIGA_S = RIGA_S + 1
> 'AND COPY IN wsStorico
> Set C = wsStorico.Columns("R:R").FindNext(C)
> End If
> RIGA = RIGA + 1
> Wend
> Application.ScreenUpdating = True
> End Sub
>
> --
> Hope this helps
> Martin Fishlock
> Please do not forget to rate this reply.
>
>
> "sal21" wrote:
>
> > In effect, in column R of sheet H7469 i have a unique index value.
> > In column R of sheet STORICO_H7469 have a unique index value.
> >
> > Noew i want recopy all range of cells from H7469 in STORICO_H7469 only
> > if in this sheet not is already present a unique index from column R of
> > H7469....
> >
> > Example:
> > admiting in H7469 column R & riga INDEX
> > ="5236220-H7469-CON-4500-04/12/2006" and in column R of sheet
> > STORICO_H7469 not is present this value copy all range of cells in
> > sheet STORICO_H7469 , insted if this INDEX is present in in column R
> > of sheet STORICO_H7469 skip the cells copy and return to find next
> > INDEX... ecc....
> >
> > other suggestion to speed code are welcome...
> > Sub FIND()
> >
> > Dim RIGA As String
> > RIGA = 3
> > Dim C As Range
> >
> > RIGA_S = Sheets("H7469_STORICO").Cells(65536, 1).End(xlUp).Row + 1
> >
> > While Not Sheets("H7469").Range("A" & RIGA) = ""
> >
> > INDEX = Sheets("H7469").Range("R" & RIGA)
> >
> > Set C =
> > Sheets("H7469_STORICO").Columns("R:R").FIND((INDEX), LookIn:=xlValues)
> >
> > If Not C Is Nothing Then
> >
> > firstAddress = C.Address
> >
> > Do
> > 'COPY IF NOT FOUND IN Sheets("H7469_STORICO")
> > Sheets("H7469_STORICO").Range("A" & RIGA_S).Value =
> > Sheets("H7469").Range("A" & RIGA).Value
> > Sheets("H7469_STORICO").Range("B" & RIGA_S).Value =
> > Sheets("H7469").Range("B" & RIGA).Value
> > Sheets("H7469_STORICO").Range("C" & RIGA_S).Value =
> > Sheets("H7469").Range("C" & RIGA).Value
> > Sheets("H7469_STORICO").Range("D" & RIGA_S).Value =
> > Sheets("H7469").Range("D" & RIGA).Value
> > Sheets("H7469_STORICO").Range("E" & RIGA_S).Value =
> > Sheets("H7469").Range("E" & RIGA).Value
> > Sheets("H7469_STORICO").Range("F" & RIGA_S).Value =
> > Sheets("H7469").Range("F" & RIGA).Value
> > Sheets("H7469_STORICO").Range("G" & RIGA_S).Value =
> > Sheets("H7469").Range("G" & RIGA).Value
> > Sheets("H7469_STORICO").Range("H" & RIGA_S).Value =
> > Sheets("H7469").Range("H" & RIGA).Value
> > Sheets("H7469_STORICO").Range("I" & RIGA_S).Value =
> > Sheets("H7469").Range("I" & RIGA).Value
> > Sheets("H7469_STORICO").Range("J" & RIGA_S).Value =
> > Sheets("H7469").Range("J" & RIGA).Value
> > Sheets("H7469_STORICO").Range("K" & RIGA_S).Value =
> > Sheets("H7469").Range("K" & RIGA).Value
> > Sheets("H7469_STORICO").Range("L" & RIGA_S).Value =
> > Sheets("H7469").Range("L" & RIGA).Value
> > Sheets("H7469_STORICO").Range("M" & RIGA_S).Value =
> > Sheets("H7469").Range("M" & RIGA).Value
> > Sheets("H7469_STORICO").Range("N" & RIGA_S).Value =
> > Sheets("H7469").Range("N" & RIGA).Value
> > Sheets("H7469_STORICO").Range("O" & RIGA_S).Value =
> > Sheets("H7469").Range("O" & RIGA).Value
> > Sheets("H7469_STORICO").Range("P" & RIGA_S).Value =
> > Sheets("H7469").Range("P" & RIGA).Value
> > Sheets("H7469_STORICO").Range("Q" & RIGA_S).Value =
> > Sheets("H7469").Range("Q" & RIGA).Value
> > Sheets("H7469_STORICO").Range("R" & RIGA_S).Value =
> > Sheets("H7469").Range("R" & RIGA).Value
> > RIGA_S = RIGA_S + 1
> > 'AND COPY IN Sheets("H7469_STORICO")
> >
> > Set C =
> > Sheets("H7469_STORICO").Columns("R:R").FindNext(C)
> >
> > Loop While Not C Is Nothing And C.Address <>
> > firstAddress
> >
> > End If
> >
> > RIGA = RIGA + 1
> >
> > Wend
> >
> > End Sub