I am setting a specific range (A1:F29) and need to determine if a cell
contains a specific string, "CV7". This string an extension/suffix of a
larger string. If the string in the cell exists I would like to copy the
value of the cell and place it in a separate worksheet, worksheet 2. If the
string does not exist we should move on to the next cell within the range.

I am not sure how to sniff out the specific string.

Thanks,

sub copyData()
Dim cell as Range, rw as Long
rw = 2
for each cell in ActiveSheet.Range("A1:F29")
if instr(1,cell,"CV7",vbTextcompare) then
worksheets("Sheet2").Cells(rw,1).Value = cell.Value
rw = rw + 1
end if
next
End sub

--
Regards,
Tom Ogilvy


"Pablo" wrote:

I am setting a specific range (A1:F29) and need to determine if a cell
contains a specific string, "CV7". This string an extension/suffix of a
larger string. If the string in the cell exists I would like to copy the
value of the cell and place it in a separate worksheet, worksheet 2. If the
string does not exist we should move on to the next cell within the range.

I am not sure how to sniff out the specific string.

Thanks,

Look at:

Sub pablo()
Dim r1 As Range, r2 As Range
Set r1 = Sheets("s1").Range("A1:F29")
Set r2 = Sheets("s2").Range("A1")
For Each r In r1
If InStr(r.Value, "CV7") < 0 Then
r2.Value = r.Value
r2.Offset(0, 1) = r.Address
Exit Sub
End If
Next
End Sub


We look for CV7 in sheet s1. If it is found, the contents of the cell are
deposited in cell A1 in sheet s2.

I also put the address where the data was found in cell B1. You can remove
this if you don't need it.
--
Gary's Student
gsnu200704


"Pablo" wrote:

I am setting a specific range (A1:F29) and need to determine if a cell
contains a specific string, "CV7". This string an extension/suffix of a
larger string. If the string in the cell exists I would like to copy the
value of the cell and place it in a separate worksheet, worksheet 2. If the
string does not exist we should move on to the next cell within the range.

I am not sure how to sniff out the specific string.

Thanks,

Tom & Gary

Thank you very much. This saved a lot of time.

"Tom Ogilvy" wrote:

sub copyData()
Dim cell as Range, rw as Long
rw = 2
for each cell in ActiveSheet.Range("A1:F29")
if instr(1,cell,"CV7",vbTextcompare) then
worksheets("Sheet2").Cells(rw,1).Value = cell.Value
rw = rw + 1
end if
next
End sub

--
Regards,
Tom Ogilvy


"Pablo" wrote:

I am setting a specific range (A1:F29) and need to determine if a cell
contains a specific string, "CV7". This string an extension/suffix of a
larger string. If the string in the cell exists I would like to copy the
value of the cell and place it in a separate worksheet, worksheet 2. If the
string does not exist we should move on to the next cell within the range.

I am not sure how to sniff out the specific string.

Thanks,