Finding a given string in a cell

  • Thread starter Thread starter palie
  • Start date Start date
P

palie

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
 
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


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
guide to creating financial statements
 
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,xlErrors)
On Error goto 0
if not rng is nothing then
rng.clearcontents
End If
 
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.
(e-mail address removed)
-----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


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

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