Finding a given string in a cell
 

Hi

I’m abit new at this and need help to do the following by creating a
macro in visual basic.

I have a table in excel 2000 with many columns and a range of rows that
can vary time to time. (the below example only shows two columns and 5
rows to simplify it)

The problem is that i want to search the first column (ID) for DR_*
(where * is any string after DR_). If it finds it in the 1st column
then it puts R in the next cell to the right (i.e in the Group column.
On the other hand i also want to search for a string starting with
"DW_" in 1st column and if it finds it, it puts a W in the group
column. Is there any way to do this?

Example....
ID Group
DR_ALM1 R
DR_ALM2 R
DR_ALM3 R
DW_ALM1 W
DW_ALM2 W

Any help grateful

Thanks


palie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Finding a given string in a cell
 

In a macro, use the InStr() function

For Each tgtCell In tgtRange
If Instr(1, tgtCell, "DR_", vbTextCompare) 0 Then
tgtCell.Offset(0, 1).Value = "R"
ElseIf Instr(1, tgtCell, "DW_", vbTextCompare) 0 Then
tgtCell.Offset(0, 1).Value = "W"
End If
Next tgtCell

in the worksheet, can use the SEARCH function, e.g.
in adjoining cell (e.g. B1), put the formula
=IF(ISERROR(SEARCH("DR_*", A1)), IF(ISERROR(SEARCH("DW_*",
A1)),"","W"),"R")

Kevin Beckham

-----Original Message-----

Hi

I'm abit new at this and need help to do the following by
creating a
macro in visual basic.

I have a table in excel 2000 with many columns and a
range of rows that
can vary time to time. (the below example only shows two
columns and 5
rows to simplify it)

The problem is that i want to search the first column
(ID) for DR_*
(where * is any string after DR_). If it finds it in the
1st column
then it puts R in the next cell to the right (i.e in the
Group column.
On the other hand i also want to search for a string
starting with
"DW_" in 1st column and if it finds it, it puts a W in
the group
column. Is there any way to do this?

Example....
ID Group
DR_ALM1 R
DR_ALM2 R
DR_ALM3 R
DW_ALM1 W
DW_ALM2 W

Any help grateful

Thanks


palie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Finding a given string in a cell
 

Just a few added thoughts:

Use of SEARCH in this case is inprecise in that it would react to

DM_DR_WA

While this may not be an issue, I believe it would be clearer to use LEFT.

=if(Left(A1,3)="DR_","R",if(left(A1,3)="DW_","W"," "))

Also, the use of "*" in the Search argument isn't necessary.

If using a macro, if you have a lot of data, it might be beneficial to have
the macro enter the formula in all the cells in GROUP in one command and
then replace them with the calculated value rather than looping throught the
range.

Assuming starting in B2

Dim tgtRange as Range, rng as Range
set tgtRange = Range(Cells(2,1),Cells(2,1).End(xldown))
tgtRange.Offset(0,1).Formula = _
"=if(Left(A2,3)=""DR_"",""R"",if(left(A2,3)=""DW_" ",""W"",NA()))"
tgtRange.Offset(0,1).Formula = tgtRange.Offset(0,1).Value
on Error resume Next
set rng = tgtRange.Offset(0,1).SpecialCells(xlconstants,xlEr rors)
On Error goto 0
if not rng is nothing then
rng.clearcontents
End If


--
Regards,
Tom Ogilvy

"Kevin Beckham" wrote in message
...
In a macro, use the InStr() function

For Each tgtCell In tgtRange
If Instr(1, tgtCell, "DR_", vbTextCompare) 0 Then
tgtCell.Offset(0, 1).Value = "R"
ElseIf Instr(1, tgtCell, "DW_", vbTextCompare) 0 Then
tgtCell.Offset(0, 1).Value = "W"
End If
Next tgtCell

in the worksheet, can use the SEARCH function, e.g.
in adjoining cell (e.g. B1), put the formula
=IF(ISERROR(SEARCH("DR_*", A1)), IF(ISERROR(SEARCH("DW_*",
A1)),"","W"),"R")

Kevin Beckham

-----Original Message-----

Hi

I'm abit new at this and need help to do the following by
creating a
macro in visual basic.

I have a table in excel 2000 with many columns and a
range of rows that
can vary time to time. (the below example only shows two
columns and 5
rows to simplify it)

The problem is that i want to search the first column
(ID) for DR_*
(where * is any string after DR_). If it finds it in the
1st column
then it puts R in the next cell to the right (i.e in the
Group column.
On the other hand i also want to search for a string
starting with
"DW_" in 1st column and if it finds it, it puts a W in
the group
column. Is there any way to do this?

Example....
ID Group
DR_ALM1 R
DR_ALM2 R
DR_ALM3 R
DW_ALM1 W
DW_ALM2 W

Any help grateful

Thanks


palie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

Finding a given string in a cell
 

Thanks for your reply the code works a treat,

pali

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Finding a given string in a cell
 

I do it all the time like this:

Assume ID is in column A and Group is in B

In B2 I would wirte the following:

=IF(left(A2,2)="DR","R","W")
copy this to each cell under Group. This assumes that "R"
and "W" are the only choices.


If you have many different types of groups, you could
consider a vlookup() statement.

=vlookup(Left(A2,2),a100:b110,2,false)

Where in a100 through b110 you have the following table
built.

A B
100 DR R
101 DW W
102 DC C
103 XX I

Hope this helps,

Chris B.


-----Original Message-----

Hi

I'm abit new at this and need help to do the following by
creating a
macro in visual basic.

I have a table in excel 2000 with many columns and a
range of rows that
can vary time to time. (the below example only shows two
columns and 5
rows to simplify it)

The problem is that i want to search the first column
(ID) for DR_*
(where * is any string after DR_). If it finds it in the
1st column
then it puts R in the next cell to the right (i.e in the
Group column.
On the other hand i also want to search for a string
starting with
"DW_" in 1st column and if it finds it, it puts a W in
the group
column. Is there any way to do this?

Example....
ID Group
DR_ALM1 R
DR_ALM2 R
DR_ALM3 R
DW_ALM1 W
DW_ALM2 W

Any help grateful

Thanks


palie


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.