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

[Andrew Fergus]

Re: June/July 2008 Challenge of the Month

Hi Emma

If you set the iteration to 1 (and keep pressing F9) you can watch how it progresses through the loop.

I also got to thinking about this yesterday - in particular my comment that you cannot loop using a formula. I (temporarily) forgot that you can by using a row() function in an array formula. I tried tweaking this to remove the helper cell, using a row() function and converting the formula to an array formula, but I got stuck and really should get on with my work.....

It's all good fun nonetheless.


Andrew

P.S. the real challenge would be returning the phrases in the order they are found in the phrase being examined!

 

[cornflakegirl]

Re: June/July 2008 Challenge of the Month

Hehehe - I did exactly that - it was great!
I also thought of row() as soon as I saw your comment, but got stuck because you can't use CONCATENATE on an array, and I couldn't think of another way to do it. It was fun trying though!

 

[drwhittle]

Re: June/July 2008 Challenge of the Month

Here's another user-defined function. I tried to make it simulate a VLOOKUP function, except that the last argument gives the option for a case-sensitive lookup. It needs more error handling and it doesn't handle the case of multiple colors being in the same line (the first one found in the list wins), but then that wasn't a requirement.

Public Function DaveLookup(lookup_value, table_array As Range, col_index_num As Integer, Optional compare_option As Integer)
    Dim MyRow
    Dim iRow As Long
    Dim MyPos As Integer
    Dim Found As Boolean
 
    On Error GoTo Err
 
    Found = False
 
    With table_array
        iRow = 1
        For Each MyRow In table_array.Rows
 
            MyPos = InStr(1, lookup_value, .Cells(iRow, 1).Value, compare_option)
 
            If Not IsNull(MyPos) And MyPos > 0 Then
                DaveLookup = .Cells(iRow, col_index_num).Value
                Exit Function
            End If
            iRow = iRow + 1
        Next
    End With
Err:
    DaveLookup = "#N/A"
End Function

 

[squiresk]

Re: June/July 2008 Challenge of the Month

Greeting from Oz..

Certainly not the most sublime version, but I got it to work eventually. I learnt so much tackling this

Sub Macro1()
Application.ScreenUpdating = False
Dim PhraseCount As Integer
Dim TableCount As Integer
Dim TableArray() As Variant
Dim i As Integer
Dim j As Integer
Dim PositionFound As Integer
TableCount = Range("D65536").End(xlUp).Row   'Find how many records in the lookup table
PhraseCount = Range("A65536").End(xlUp).Row  'Find how many records to be looked up
ReDim TableArray(TableCount, 2)         ' Resize the array
TableArray = Range("D2:E" & TableCount).Value   ' Shove the lookup table into an array
On Error Resume Next    ' Had to include this, because at the end, after all the ForNext'ing we get a
                        ' subscript out of range error.
                        ' I do not understand why
For j = 1 To TableCount    ' Loop through all the records to be searched
                           ' Then loop through each lookup value.
                           ' If it finds it, enter the corresponding lookup table value
    For i = 1 To PhraseCount
            PositionFound = InStr(Cells(i, 1), TableArray(j, 1))
        If PositionFound > 0 Then
        Cells(i, 2) = TableArray(j, 2)
        End If
    Next
Next
Application.ScreenUpdating = True
End Sub

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

Hello, Oz!

If you still want to learn a little more, consider these items:

You don't need to resize the array. Since you've dimmed TableArray as a variant, just go ahead with your next line where you set the variant to the range. Excel will automatically size the array for you as if you redimmed it like this:

ReDim TableArray(0 to TableCount - 1, 0 to 1)

There's a clue there to why you had to add the error checking. Although you did not specify (0 to TableCount) in your ReDim, unless you specifically instruct the VBA otherwise it will start at zero anyways. You have started your For Next loop at 1. Since the data will be in the array stating at element 0, you will run out of data just before the last loop cycle, raising the error. You are also skipping the first data at element 0.

To fix this just change your For Next loop for j to this:

For j = 0 to TableArray - 1

You should also change your 2nd dimension references downward by 1. So TableArray(j, 1) would become TableArray(j, 0)

and

TableArray(j, 2) would become TableArray(j, 1)

This change is required for how Excel will automatically resize the array. You won't need the Error traping, but that is never a bad thing.

You don't need the PositionFound variable. You could replace the two lines that currently make use of it in the For i loop with:

If Instr(Cells(i, 1), TableArray(j, 0)) Then

But all and all, a very good effort. Try my changes and see what you think. Those will help you in almost every VBA program.

Regards,

Daniel

 

[squiresk]

Re: June/July 2008 Challenge of the Month

Thanks very much..

I'll will go through it again. I guess the main thing is the array 'elements' begin at zero, not 1.

Kai, (in Austrialia)

 

[Daniel Ferry]

Re: June/July 2008 Challenge of the Month

Kai,

Unless you specifiy otherwise, yes, all array elements will begin at zero.

But let VBA resize the variant for you; no need to do the ReDim yourself unless you really needed the dimensions to begin at different numbers (which you don't).

When I optimize my code I alway look for lines and variables I can eliminate. That's why I spent a moment pointing some out. Less code is usually better.

I'll leave you with another thing to think about. Loops are great, but they can very quickly slow down your code, if there were hundreds or even thousands of elements in the lookup tables. In this exercise it is academic since there are only 10 elements in each.

But for learning purposes, it would be a good thing if you could figure-out a way to eliminate one of the loops or, better yet, both. This can be done, and would be a really great achievement. A clue is that Excel has many wonderful worksheet functions that can be called from VBA (i.e., WorksheetFunctions.Find). With some ingenuity, these can be used in a great many situations to remove loops from VBA code. To be sure, looping is still going on, but it is being done by very fast, compiled C++ code, and not poky interpreted VBA. In a real-world application this can dramatically increase your code execution speed, in some cases from minutes to run a given loop, improved to requiring just a fraction of a second with the method described.

Anyways, happy coding.

Regards,

Daniel

 

[Jerry0]

Re: June/July 2008 Challenge of the Month

Here's my VBA code entry.

Sub JuneChallenge()

LastRowColA = Range("A65536").End(xlUp).row
LastRowColD = Range("D65536").End(xlUp).row

For Each cel In Range("D2:D" & LastRowColD)
keyword = cel: Assigned = cel.Offset(0, 1)

With Worksheets(1).Range("a1:a" & LastRowColA)
Set C = .Find(keyword, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
C.Offset(0, 1).Value = Assigned
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With

Next cel

End Sub

 

[Emanon]

Re: June/July 2008 Challenge of the Month

This was my first solution. The method has been used by others though.

=INDEX(E$2:E$10,MATCH(FALSE,ISERR(SEARCH(D$2:D$10,A3)),0))

 

[Richard Schollar]

Re: June/July 2008 Challenge of the Month

Hi Daniel/Kai

That's not true that all array elements begin at zero - variant arrays created by assigning a range to a variant are always 2 dimensional 1-based arrays.

Arrays created via using the Array function or by using Dim statements where you don't specify the lower bound (eg Dim myArray(10) As String) will use the module's Option Base statement (where the default value is 0 if you haven't specified it) to set the lower bound - hence if you use Option Base 1, then your arrays will be 1-based.

Certain functions (such as Split) return 0-based arrays no matter what the option base setting. Additionally, using the VBA prefix in front of the Array function (eg x = VBA.Array(1,2,3)) will always return a 0-based array no matter what the Option Base setting.

Because, dependent on how an array is created and what Option Base setting is active, arrays may or may not have a 0-base you will frequently see loop code written to account for this whereby the lower bound is determined by the LBound function:

For i = LBound(myArray) To UBound(myArray) Step 1

in which case you never have to worry about how the array has been declared/created. This is especially useful when creating class modules as you may have no power over the class user to specify what base is used when they create an array that they might pass to the Class instance.