How to determine if a cell has a value, then copy it to a new loca
 

Hello,

I need to determine if a cell in one worksheet has a specific string of
characters, and if yes, copy the entire cell to a second worksheet.

So,

If 'Worksheet1!a1' contains the string "ACS", copy the content of
'Worksheet1!a1' to 'Worksheet2!a1'

I've tried several things I've found on this BB, but not luck so far.

THANKS!

How to determine if a cell has a value, then copy it to a new loca
 

=IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1!a1'))),'W orksheet1!a1',"") ???

Dave
--
Brevity is the soul of wit.


"Shelly" wrote:

Hello,

I need to determine if a cell in one worksheet has a specific string of
characters, and if yes, copy the entire cell to a second worksheet.

So,

If 'Worksheet1!a1' contains the string "ACS", copy the content of
'Worksheet1!a1' to 'Worksheet2!a1'

I've tried several things I've found on this BB, but not luck so far.

THANKS!

How to determine if a cell has a value, then copy it to a new loca
 

Try this small macro:

Sub shelley()
Set r1 = Sheets("Sheet1").Range("A1")
Set r2 = Sheets("Sheet2").Range("A1")
v = r1.Value
If InStr(v, "ACS") 0 Then
r1.Copy r2
End If
End Sub

If you are unfamiliar with VBA, See:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Gary's Student
gsnu200702


"Shelly" wrote:

Hello,

I need to determine if a cell in one worksheet has a specific string of
characters, and if yes, copy the entire cell to a second worksheet.

So,

If 'Worksheet1!a1' contains the string "ACS", copy the content of
'Worksheet1!a1' to 'Worksheet2!a1'

I've tried several things I've found on this BB, but not luck so far.

THANKS!

How to determine if a cell has a value, then copy it to a new loca
 

Formulas retrieve values--not really copy.

=if(countif('sheet1'!a1,"*acs*")0,'sheet1'!a1,"wh ateveryouwanthere")



Shelly wrote:

Hello,

I need to determine if a cell in one worksheet has a specific string of
characters, and if yes, copy the entire cell to a second worksheet.

So,

If 'Worksheet1!a1' contains the string "ACS", copy the content of
'Worksheet1!a1' to 'Worksheet2!a1'

I've tried several things I've found on this BB, but not luck so far.

THANKS!

--

Dave Peterson

How to determine if a cell has a value, then copy it to a new loca
 

Watch your apostrophes:

=IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1'!a1))),'W orksheet1'!a1,"")

And sometimes just using =isnumber() makes the formula easier to read:

=IF(ISnumber(SEARCH("ACS",'Worksheet1'!a1)),'Works heet1'!a1,"")




Dave F wrote:

=IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1!a1'))),'W orksheet1!a1',"") ???

Dave
--
Brevity is the soul of wit.

"Shelly" wrote:

Hello,

I need to determine if a cell in one worksheet has a specific string of
characters, and if yes, copy the entire cell to a second worksheet.

So,

If 'Worksheet1!a1' contains the string "ACS", copy the content of
'Worksheet1!a1' to 'Worksheet2!a1'

I've tried several things I've found on this BB, but not luck so far.

THANKS!

--

Dave Peterson

How to determine if a cell has a value, then copy it to a new
 

Thanks for the quick replies... I've tried the macro and ISERROR, but now I
see the need is more comlicated.

The range that I am searching is C33 through C77 (in Worksheet1)
If any of these cells contain the string "ACS"
I need the value of that cell to also be put into a cell in the 2nd worksheet
but the range for this is only A8 through A20

So the "target" isn't the same cell as the "source".

At no time will the source list (C33 - C77) return more values than will fit
in the target list (A8 - A20).

Much Thanks!

How to determine if a cell has a value, then copy it to a new
 

Good points, all. Agreed about ISNUMBER().

Dave
--
Brevity is the soul of wit.


"Dave Peterson" wrote:

Watch your apostrophes:

=IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1'!a1))),'W orksheet1'!a1,"")

And sometimes just using =isnumber() makes the formula easier to read:

=IF(ISnumber(SEARCH("ACS",'Worksheet1'!a1)),'Works heet1'!a1,"")




Dave F wrote:

=IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1!a1'))),'W orksheet1!a1',"") ???

Dave
--
Brevity is the soul of wit.

"Shelly" wrote:

Hello,

I need to determine if a cell in one worksheet has a specific string of
characters, and if yes, copy the entire cell to a second worksheet.

So,

If 'Worksheet1!a1' contains the string "ACS", copy the content of
'Worksheet1!a1' to 'Worksheet2!a1'

I've tried several things I've found on this BB, but not luck so far.

THANKS!

--

Dave Peterson

How to determine if a cell has a value, then copy it to a new
 

I'd apply data|Filter|autofilter and filter C33:C77 to show the cells that
contain ACS and then copy those visible rows to that other range.

Shelly wrote:

Thanks for the quick replies... I've tried the macro and ISERROR, but now I
see the need is more comlicated.

The range that I am searching is C33 through C77 (in Worksheet1)
If any of these cells contain the string "ACS"
I need the value of that cell to also be put into a cell in the 2nd worksheet
but the range for this is only A8 through A20

So the "target" isn't the same cell as the "source".

At no time will the source list (C33 - C77) return more values than will fit
in the target list (A8 - A20).

Much Thanks!

--

Dave Peterson