Not find 3

[ferdo]

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

Regards
Fernando

 

[palusa]

Fernando,
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:

Private Sub CommandButton1_Click()
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

 

[joerd]

Without VBA, you could use VLOOKUP, or MATCH and INDEX type constructions to see if the value exists in the second sheet. Then, you can sort out the N/A errors (i.e. values not found in the second sheet), and paste them elsewhere. If you need more guidance, let the forum know.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ferdo]

Hi,

Thanks to both of you for the answers.

Everyday I'm learning something...



Regards
Fernando