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

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">
user_offline.gif
</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>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
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
 
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.
 
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,
 
Re: June/July 2008 Challenge of the Month

the length of the String for searching
 
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)
 
Last edited:
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))))))
 
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)
 

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top