***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[andrewman]

Re: June/July 2008 Challenge of the Month

<TABLE class=tborder id=post1626550 cellSpacing=0 cellPadding=6 width="100%" align=center border=0><TBODY><TR vAlign=top><TD class=alt1 id=td_post_1626550 style="BORDER-RIGHT: #ffffff 1px solid">VBA can use the Regular Expression which just like OpenOffice worked.

Jindon has demonstrated the method how to use the Regular Expression in VBA.

Do you think is suitable?

Andrew Man
From Hong Kong
<!-- / message --></TD></TR><TR><TD class=alt2 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 1px solid; BORDER-BOTTOM: #ffffff 1px solid">

</TD><TD class=alt1 style="BORDER-RIGHT: #ffffff 1px solid; BORDER-TOP: #ffffff 0px solid; BORDER-LEFT: #ffffff 0px solid; BORDER-BOTTOM: #ffffff 1px solid" align=right><!-- controls --></TD></TR></TBODY></TABLE>

 

[kevinmintz006]

Re: June/July 2008 Challenge of the Month

sorry i am late, i am a new user as of today = ).

here's my solution (macro code):


Sub Solution()
'
' Solution Macro
' Macro recorded 7/17/2008 by e363131
'
'
Dim i As Long, strx As String, stry As String, strz As String
i = 2
Range("A:A").Select

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Copy
Columns("B:B").Select
ActiveSheet.Paste

For i = 1 To 10


strx = "D" & i
'MsgBox (strx)
stry = Range(strx).Value
'MsgBox (stry)
strz = "~*" & stry & "~*"
Selection.Replace What:="*" & Range(strx).Value & "*", Replacement:="=E" & i, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next
End Sub



It's good to be partof the community.

 

[jimnaff]

Re: June/July 2008 Challenge of the Month

I know it's past the deadline, but I thought I would offer this:

Public Function AssignTo(Phrase As String, KeyWordList As Range) As String
Dim i As Integer
For i = 1 To KeyWordList.Rows.Count
If InStr(1, Phrase, KeyWordList(i, 1)) > 0 Then
AssignTo = KeyWordList(i, 2)
Exit For
End If
Next i
End Function

 

[jindon]

Re: June/July 2008 Challenge of the Month

VBA can use the Regular Expression which just like OpenOffice worked.

Jindon has demonstrated the method how the use the Regular Expression in VBA.

Do you think is suitable?

Andrew Man
From Hong Kong

Andrew,

I think what Regular Expression does can be done by vba built-in functions anyhow.
However the length of the code will be much shorter when it is used sometime.

 

[andrewman]

Re: June/July 2008 Challenge of the Month

Yes, the code will be shorter.

But, it will be a limitation in the length of the String for searching.

Regards,

 

[jindon]

Re: June/July 2008 Challenge of the Month

Yes, the code will be shorter.

But, it will be a limitation in the length of the String for searching.

Regards,

What limitation?

 

[andrewman]

Re: June/July 2008 Challenge of the Month

the length of the String for searching

 

[bosco_yip]

Re: June/July 2008 Challenge of the Month

1] =lookup(2,1/find(d$2:d$10,a2),e$2:e$10)

2] =lookup(0,1/find(d$2:d$10,a2)-1,e$2:e$10)

3] {=index($e$2:$e$10,match(1,--isnumber(find($d$2:$d$10,a2)),0),0)}

Add 2 more formula :

4] =LOOKUP(0,-FIND(D$2:D$10,A2),E$2:E$10)

5] =LOOKUP(0,FIND(D$2:D$10,A2)-1,E$2:E$10)

 

[U.Surender]

Re: June/July 2008 Challenge of the Month

I am pretty new to the Excel game so any help would be great!! Why doesn't this work??

=IF(ISERR(SEARCH("blue",A2))=FALSE,"Joe",IF(ISERR(SEARCH("red",A2))=FALSE,"Bob",IF(ISERR(SEARCH("yellow",A2))=FALSE,"Mary",IF(ISERR(SEARCH("pink",A2))=FALSE,"Fred",IF(ISERR(SEARCH("orange",A2))=FALSE,"Ralph",IF(ISERR(SEARCH("brown",A2))=FALSE,"Lora",33))))))

 

[EugeneCarter]

Re: June/July 2008 Challenge of the Month

1) doesn't use the lookup table (you manually entered lookup values)
2) not scalable (excel can only support so many recursive IFs)