CODEWITHSUNDEEP
stringexceltextfindvba
FotoDJ
FotoDJ

Reputation: 351

Copy if part of the text cell found excel VBA

I am having a hard time to write that VBA...

Attached picture should help to understand what I need to do

Problem:

a) If text "FAA" is found in Sheet1 Column B then copy cell C in the same row to Sheet2 Column A.

enter image description here

then,

b) Delete those cells in row B and C of Sheet1.

Upvotes: 0

Views: 778

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

With Worksheets("Sheet1")

    For each cel in .Range(.Range("B1"),.Range("B1").End(xlDown))

       If cel.Value Like "*FAA*" Then 
           Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = cel.Offset(,1).Value
          cel.Resize(1,2).ClearContents

       End If

    Next

   'if you wish to remove the rows entirely use this code, if not comment out   
   .Range(.Range("B1"),.Range("A1").End(xlDown).Offset(,1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End With

Upvotes: 2

Related Questions