Hi,

I'm trying to write a macro in excel to find a predefined codes ina
sequence of codes in a single cell, if the any of the codes match the
predefined codes macro has to cut that particualr row and paste the
same in another spreadsheet.


Example :-


Loan# Exclude 1 Exclude 2
123465 LI,SA sa,li,3f
123466 dd sf,dd,ex,gh,LIP
123467 co cop
123468 aa sf,dd,ex,gh,LIP
123469 fdf sa,li,3f
123470 fdf cop
123471 df co
123472 dd aa
123473 dd fdf


if in first row 3f is the predefined code so it has to cut this
particular row and paste in another spreadsheet for further research.


please help me on this.

Sub CopyData()
Dim rng as Range, rng1 as Range, cell as Range
With worksheets("Sheet1")
set rng = .Range(.cells(2,3),.Cells(2,3).End(xldown))
end with
for each cell in rng
if instr(1,cell,"3f",vbTextCompare) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.EntireRow.copy Destination:=Worksheets( _
"Sheet2").Range("A1")
rng1.EntireRow.Delete
end if
End Sub

This assumes you won't have codes like 3f2, which contain 3f, but would
not indicate the row should be copied.


--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hi,

I'm trying to write a macro in excel to find a predefined codes ina
sequence of codes in a single cell, if the any of the codes match the
predefined codes macro has to cut that particualr row and paste the
same in another spreadsheet.


Example :-


Loan# Exclude 1 Exclude 2
123465 LI,SA sa,li,3f
123466 dd sf,dd,ex,gh,LIP
123467 co cop
123468 aa sf,dd,ex,gh,LIP
123469 fdf sa,li,3f
123470 fdf cop
123471 df co
123472 dd aa
123473 dd fdf


if in first row 3f is the predefined code so it has to cut this
particular row and paste in another spreadsheet for further research.


please help me on this.

Hi tom,

thanks for the help, this will help me a lot, but is there way that it
should only copy rows were the code is 3f or 3F but not 3fd or 3FD or
3FF etc.
1 more problem i have is when i try to replace south or north with S &
N respectively it also changes southern to Sern and northern to Nern is
there a way to go about this.

your help will be greatly appreciated and thanks for the code.

Regards
Max


Tom Ogilvy wrote:
Sub CopyData()
Dim rng as Range, rng1 as Range, cell as Range
With worksheets("Sheet1")
set rng = .Range(.cells(2,3),.Cells(2,3).End(xldown))
end with
for each cell in rng
if instr(1,cell,"3f",vbTextCompare) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.EntireRow.copy Destination:=Worksheets( _
"Sheet2").Range("A1")
rng1.EntireRow.Delete
end if
End Sub

This assumes you won't have codes like 3f2, which contain 3f, but would
not indicate the row should be copied.


--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hi,

I'm trying to write a macro in excel to find a predefined codes ina
sequence of codes in a single cell, if the any of the codes match the
predefined codes macro has to cut that particualr row and paste the
same in another spreadsheet.


Example :-


Loan# Exclude 1 Exclude 2
123465 LI,SA sa,li,3f
123466 dd sf,dd,ex,gh,LIP
123467 co cop
123468 aa sf,dd,ex,gh,LIP
123469 fdf sa,li,3f
123470 fdf cop
123471 df co
123472 dd aa
123473 dd fdf


if in first row 3f is the predefined code so it has to cut this
particular row and paste in another spreadsheet for further research.


please help me on this.

You would have to check the possibilities

You would have to check for all the conditions:

if (len(cell) = 2 and lcase(cell) = "3f" or left(lcase(cell),3) = "3f," or
instr(1,cell,"3F,",vbTextcompare) then

note that the last check is for "3F,", not just "3F"

for you second problem,
possibly replace with "south " with "s "

Again, it depends thinking on what would make it unique.

--
Regards,
Tom Ogilvy




" wrote:

Hi tom,

thanks for the help, this will help me a lot, but is there way that it
should only copy rows were the code is 3f or 3F but not 3fd or 3FD or
3FF etc.
1 more problem i have is when i try to replace south or north with S &
N respectively it also changes southern to Sern and northern to Nern is
there a way to go about this.

your help will be greatly appreciated and thanks for the code.

Regards
Max


Tom Ogilvy wrote:
Sub CopyData()
Dim rng as Range, rng1 as Range, cell as Range
With worksheets("Sheet1")
set rng = .Range(.cells(2,3),.Cells(2,3).End(xldown))
end with
for each cell in rng
if instr(1,cell,"3f",vbTextCompare) then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = union(rng1,cell)
end if
end if
Next
if not rng1 is nothing then
rng1.EntireRow.copy Destination:=Worksheets( _
"Sheet2").Range("A1")
rng1.EntireRow.Delete
end if
End Sub

This assumes you won't have codes like 3f2, which contain 3f, but would
not indicate the row should be copied.


--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hi,

I'm trying to write a macro in excel to find a predefined codes ina
sequence of codes in a single cell, if the any of the codes match the
predefined codes macro has to cut that particualr row and paste the
same in another spreadsheet.


Example :-


Loan# Exclude 1 Exclude 2
123465 LI,SA sa,li,3f
123466 dd sf,dd,ex,gh,LIP
123467 co cop
123468 aa sf,dd,ex,gh,LIP
123469 fdf sa,li,3f
123470 fdf cop
123471 df co
123472 dd aa
123473 dd fdf


if in first row 3f is the predefined code so it has to cut this
particular row and paste in another spreadsheet for further research.


please help me on this.