I have a code (see below)
i would like to be able to check in column D if there is already the same
document number,if the code find the same number it should delete the row and
paste the new values. This VBA is not working could someone help me?

Thanks

Sub Macro1()

Dim Wk As Workbook, Rep


Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls")



Windows("excel base.xls").Activate
Range("B1:B80").Select
Selection.Copy
Windows("Databasere_validierung.xls").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
If Application.CountIf([D:D], Cells(Rows.Count, 3).End(xlUp)) 1
Then
Var = Cells(Rows.Count, 3).End(xlUp)
For i = Cells(Rows.Count, 3).End(xlUp).Row - 1 To 1 Step -1
If Cells(i, 3) = Var Then Rows(i).Delete
Next i
End If
'Wk.Close True'
End Sub

Let's take a look:

Sub Macro1()

Dim Wk As Workbook, Rep '<<<What is Rep?

'Next line is OK. It sets Wk as object variable for workbook opened.
Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls")

'Windows("excel base.xls").Activate '<<<This could be set to a variable
'Set Wk2 = Workbooks("excel base.xls"), Or
Set Wk2 = ThisWorkbook '<<<if it is the active workbook.
'Now you can use the object variables in the code.
'The worksheet is not identified, so it is assumed that Sheet1 is the
'active sheet in each workbook.
Set sh1 = Wk.Sheets("Sheet1") 'Create object variable for sheets
Set sh2 = Wk2.Sheets("Sheet1")
'here is where the problem starts. Your problem description says you
want to
'replace data in Column D if it matches a document number, so copying 80
'rows of column B in the active workbook and pasting it into column A
does
'does not follow the logic of the problem description.
Range("B1:B80").Select
Selection.Copy
Windows("Databasere_validierung.xls").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

'Then this further confuses the logic because it searches column D
'for a cell value in column C and if found it attempts to delete a row.
'based on comparison of two cells in column C.

'In the code below, everywhere cells is used requires qualification
'to an object, such as sh1 or sh2, as applicable. Otherwise, VBA
'automatically assumes active sheet. If it is all in one Workbook
'and one worksheet, then you could use a With statement like:

'With sh2
'the code
'end with
'Periods would have to be put in front of cellls (.Cells()) to attach
'them to the With statement, otherwise, they still revert to active
sheet.




If Application.CountIf([D:D], Cells(Rows.Count, 3).End(xlUp)) 1 Then

Var = Cells(Rows.Count, 3).End(xlUp) 'Last cell in Col C value

For i = Cells(Rows.Count, 3).End(xlUp).Row - 1 To 1 Step -1

If Cells(i, 3) = Var Then Rows(i).Delete 'Automatic deletion
'of last row because Var value is based on the same cell value.

Next i
End If

'Wk.Close True'
End Sub

Maybe a little more explanation about what is in which columns would help to
clear up the confusion about the copying and pasting before making the
comparison for the document number.





"Louis" wrote in message
...
I have a code (see below)
i would like to be able to check in column D if there is already the same
document number,if the code find the same number it should delete the row
and
paste the new values. This VBA is not working could someone help me?

Thanks

Sub Macro1()

Dim Wk As Workbook, Rep


Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls")



Windows("excel base.xls").Activate
Range("B1:B80").Select
Selection.Copy
Windows("Databasere_validierung.xls").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
If Application.CountIf([D:D], Cells(Rows.Count, 3).End(xlUp)) 1
Then
Var = Cells(Rows.Count, 3).End(xlUp)
For i = Cells(Rows.Count, 3).End(xlUp).Row - 1 To 1 Step -1
If Cells(i, 3) = Var Then Rows(i).Delete
Next i
End If
'Wk.Close True'
End Sub

Hello

thanks for your answer, Honestly i'm lost and i have no clue how to solve
the problem.

I'm trying to find a code which copy the column C2 to C80 'excel base.xls.
it should paste by transposing into Databasere_validierung.xls document.
if in column C ' Databasere_validierung.xls document' the same number appear
it should delete the line and paste again the new selection.

Louis


"JLGWhiz" wrote:

Let's take a look:

Sub Macro1()

Dim Wk As Workbook, Rep '<<<What is Rep?

'Next line is OK. It sets Wk as object variable for workbook opened.
Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls")

'Windows("excel base.xls").Activate '<<<This could be set to a variable
'Set Wk2 = Workbooks("excel base.xls"), Or
Set Wk2 = ThisWorkbook '<<<if it is the active workbook.
'Now you can use the object variables in the code.
'The worksheet is not identified, so it is assumed that Sheet1 is the
'active sheet in each workbook.
Set sh1 = Wk.Sheets("Sheet1") 'Create object variable for sheets
Set sh2 = Wk2.Sheets("Sheet1")
'here is where the problem starts. Your problem description says you
want to
'replace data in Column D if it matches a document number, so copying 80
'rows of column B in the active workbook and pasting it into column A
does
'does not follow the logic of the problem description.
Range("B1:B80").Select
Selection.Copy
Windows("Databasere_validierung.xls").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

'Then this further confuses the logic because it searches column D
'for a cell value in column C and if found it attempts to delete a row.
'based on comparison of two cells in column C.

'In the code below, everywhere cells is used requires qualification
'to an object, such as sh1 or sh2, as applicable. Otherwise, VBA
'automatically assumes active sheet. If it is all in one Workbook
'and one worksheet, then you could use a With statement like:

'With sh2
'the code
'end with
'Periods would have to be put in front of cellls (.Cells()) to attach
'them to the With statement, otherwise, they still revert to active
sheet.




If Application.CountIf([D:D], Cells(Rows.Count, 3).End(xlUp)) 1 Then

Var = Cells(Rows.Count, 3).End(xlUp) 'Last cell in Col C value

For i = Cells(Rows.Count, 3).End(xlUp).Row - 1 To 1 Step -1

If Cells(i, 3) = Var Then Rows(i).Delete 'Automatic deletion
'of last row because Var value is based on the same cell value.

Next i
End If

'Wk.Close True'
End Sub

Maybe a little more explanation about what is in which columns would help to
clear up the confusion about the copying and pasting before making the
comparison for the document number.





"Louis" wrote in message
...
I have a code (see below)
i would like to be able to check in column D if there is already the same
document number,if the code find the same number it should delete the row
and
paste the new values. This VBA is not working could someone help me?

Thanks

Sub Macro1()

Dim Wk As Workbook, Rep


Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls")



Windows("excel base.xls").Activate
Range("B1:B80").Select
Selection.Copy
Windows("Databasere_validierung.xls").Activate
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
If Application.CountIf([D:D], Cells(Rows.Count, 3).End(xlUp)) 1
Then
Var = Cells(Rows.Count, 3).End(xlUp)
For i = Cells(Rows.Count, 3).End(xlUp).Row - 1 To 1 Step -1
If Cells(i, 3) = Var Then Rows(i).Delete
Next i
End If
'Wk.Close True'
End Sub


.