×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Not find
3

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

Regards
Fernando

RE: Not find

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:

CODE

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

RE: Not find

2
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.

RE: Not find

(OP)
Hi,

Thanks to both of you for the answers.

Everyday I'm learning something...

Regards
Fernando

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources