I'm stucked with using Find and set as variable. I cannot get the result I need. In first sheet I have a column Test with values x or (x). If the value is x I need to copy the value from column EN. If the value is (x) do not copy.
the code copies values from column "EN" no matter x or (x)
I must have probably an error in using Set stfound
Dim ENcolumn
Dim xcolumn
Dim secrow
Dim lastrow
Dim totrow
Worksheets("List1").Activate
Worksheets("List1").Range("A1:C1").Find(What:="EN", MatchCase:=True,
lookAT:=xlWhole).Activate
ENcolumn = ActiveCell.Column 'find and create variable
Worksheets("List1").Range("A1:C1").Find(What:="test", MatchCase:=True,
lookAT:=xlWhole).Activate
xcolumn = ActiveCell.Column 'find and create variable
currow = ActiveCell.Row + 1 ''make one low rower than current row (first
value)
lastrow = Worksheets("List1").Cells(Rows.Count, xcolumn).End(xlUp).Row
For totrow = currow To lastrow
Set stfound = Cells.Find(What:="x", After:=Cells(totrow, xcolumn),
MatchCase:=True, lookAT:=xlWhole)
If Not stfound Is Nothing Then 'if value is found then do this
Worksheets("List1").Cells(totrow, ENcolumn).Copy 'copy values
Worksheets("List2").Activate
b = Worksheets("list2").Cells(Rows.Count, ENcolumn).End(xlUp).Row
Worksheets("list2").Cells(b + 1, 2).Select 'select first empty cell in
second column
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Value = "receivercode"
ActiveCell.Offset(0, 2).Value = "01.01.2019"
Worksheets("list1").Activate
End If
Next
Application.CutCopyMode = False 'stop if false
ThisWorkbook.Worksheets("List1").Cells(1, 1).Select
MsgBox ("done")`
Now I get all the values from column "EN" copied to sheet2 to column2.
I need only those values in column EN that has x value in column 1
Aucun commentaire:
Enregistrer un commentaire