Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the cell with a specified string | Excel Worksheet Functions | |||
Finding the last cell to enter a string and/or a sum formula | Excel Worksheet Functions | |||
finding a name in a string | Excel Worksheet Functions | |||
Finding a text string w/in a Cell | Excel Discussion (Misc queries) | |||
Finding a string | Excel Programming |