Not find
Not find
(OP)
Hi,
I have a column with data in Sheet1 and same data concatenate with something else in Sheet2, indifferent columns.
I'm doing a search for each cell from first sheet to see if I have it in the second one and if I found it, I take the whole text from the cell where I found it, and paste near first column in first sheet, in the second column.
Now its something which I want to do but it seems I'm "blind" and I can't see the solution.
If I cannot find the value in the second sheet, I want to cut-paste this value in another sheet and delete the entire row in first sheet.
Any clue how to do it, please?
Regards
Fernando
I have a column with data in Sheet1 and same data concatenate with something else in Sheet2, indifferent columns.
I'm doing a search for each cell from first sheet to see if I have it in the second one and if I found it, I take the whole text from the cell where I found it, and paste near first column in first sheet, in the second column.
Now its something which I want to do but it seems I'm "blind" and I can't see the solution.
If I cannot find the value in the second sheet, I want to cut-paste this value in another sheet and delete the entire row in first sheet.
Any clue how to do it, please?
Regards
Fernando
Regards
Fernando





RE: Not find
below my solution. I have 1st part of data in Sheet1 cols A1:A20, same part + something else in Sheet2 cols F1:F20 and the range in Sheet3 for unfound starts in C1.
On sheet1 I have a button that activates the following macro:
CODE
Dim rS1 As Range, rS2 As Range, rS3 As Range, r0 As Range
Dim Found As Boolean, iR As Integer, iCnt As Integer, iRw As Integer
On Error Resume Next
Set rS1 = Worksheets("Sheet1").Range("A1:A20")
Set rS2 = Worksheets("Sheet2").Range("F1:F20")
Set rS3 = Worksheets("Sheet3").Range("C1")
Found = True
iCnt = 1
iRw = rS1.Rows.Count
Do
iR = 0
Set r0 = Range(rS1.Item(iCnt, 1).Address)
If Found = False Then
r0.Offset(-1, 0).EntireRow.Activate
r0.Offset(-1, 0).EntireRow.Delete
r0.Select
iCnt = iCnt - 1
Set r0 = Range(rS1.Item(iCnt, 1).Address)
End If
target = r0.Value & "*"
iR = WorksheetFunction.Match(target, rS2, 0)
If iR > 0 Then
Found = True
r0.Offset(0, 1) = rS2.Item(iR, 1)
Else
rS3 = r0
Set rS3 = rS3.Offset(1, 0)
Found = False
iRw = iRw - 1
End If
iCnt = iCnt + 1
Loop While iCnt <= iRw
End Sub
HTH
_Lf
RE: Not find
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Not find
Thanks to both of you for the answers.
Everyday I'm learning something...
Regards
Fernando