I created a function that runs too slow! Can you help me?

[LuisEngineer]

Hi everyone!

I just created a FUNCTION to find the column number of a cell in other BOOK depending of two variables. For 100 cells it takes 3 minutes to calculate!

THIS IS MY CODE:

     
Option Explicit


Function ENCUENTRASEM(takt As Double, wagon As String) As Double

Dim col As Integer
Dim row As Integer
Dim res As Integer

Dim NumRows As Integer
Dim NumCols As Integer
NumRows = 370
NumCols = 148

res = -1
For row = 8 To NumRows
    For col = 5 To NumCols
    
        If Application.Workbooks("SHM_170720_0300_TSH_CHU_Takt-Schedule_V049").Worksheets("3.TAKT SCHEDULE").Cells(row, col).Value = wagon And Application.Workbooks("SHM_170720_0300_TSH_CHU_Takt-Schedule_V049").Worksheets("3.TAKT SCHEDULE").Cells(row, 1).Value = takt Then
            res = col
            Exit For
        End If
        
    Next col
    
        If res <> -1 Then
            Exit For
        End If
Next row

ENCUENTRASEM = res

End Function

[bakerman2]

Deleted while OP complied to Forum Rules.

[MrShorty]

I would suggest that you come up with a better searching algorithm. As programmed, it looks like a very inefficient "start in the upper left corner and go across and down until it finds the cell that matches the two criteria". I think a better search algorithm could improve the performance of this.

First thing I notice, it appears that you are only going to return a value from the row which has takt in the leftmost column. As presently programmed, your search algorithm wastes a lot of time/effort searching across rows that do not have takt in the first column of that row. I would structure the algorithm so that it first searches for takt in the first column. Once the algorithm knows which row takt is in, then look for the column in that row that contains wagon.

Does that make sense? It appears that you already know how to code a basic linear search algorithm, so can I assume you can rearrange your loops to change the search order?

[LuisEngineer]

Hey! Thanks for your help, I've been trying to do what you said but I can't get it. Can you help me showing how would you do the arrange please?

[MrShorty]

I'd be curious what you tried. You seem to know For...Next loops and Block If's and such, so it seems like it should have been fairly straightforward. I would have expected like this code snippet:

For row=8 to numrows
If rangereference.cells(row,1).value=takt then exit For
next row
For col=5 to numcols
if rangereference.cells(row,col).value=wagon then exit for
next col
'process result

[bakerman2]

I would even lose the column search.

Function ENCUENTRASEM(takt As Double, wagon As String) As Double
    Dim NumRows As Long, NumCols As Long
    Dim col As Long, row As Long, x

    NumRows = 370
    NumCols = 148
    col = -1

    For row = 8 To NumRows
        If Workbooks("SHM_170720_0300_TSH_CHU_Takt-Schedule_V049").Worksheets("3.TAKT SCHEDULE").Cells(row, 1).Value = takt Then
            x = Application.Match(wagon, Workbooks("SHM_170720_0300_TSH_CHU_Takt-Schedule_V049").Worksheets("3.TAKT SCHEDULE").Cells(row, 5).Resize(, 148), 0)
            If Not IsError(x) Then col = x + 4: Exit For
        End If
    Next row

    ENCUENTRASEM = col
End Function

[mehmetcik]

I am sorry guys but why would anyone search through a whole lot of cells looking for specific text?

Surely you would use the Find function?

What am I missing?

Function ENCUENTRASEM(takt As Double, wagon As String) As Double

res = -1

    With Application.Workbooks("SHM_170720_0300_TSH_CHU_Takt-Schedule_V049").Worksheets("3.TAKT SCHEDULE").Range(Cells(8, 5), Cells(370, 148))
    
        Set rngFind = .Find(wagon, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Do
                If Cells(rngFind.row, 1) = takt Then res = rngFind.row
                
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress And res = -1
        End If
    End With

ENCUENTRASEM = res

End Function

[MrShorty]

mehmetcik: I did not suggest the .Find method because I was not sure how well it would work for this kind of 2D lookup, especially if there are multiple instances of wagon in the table and the goal of the lookup is to find which instance of wagon is in the takt row. The OP really hasn't given enough information for me to suggest how to use .Find in this situation (though that could easily be because I don't use .Find enough to see how to use it here).

For that matter, why use VBA at all. I expect there is a way to combine INDEX() and MATCH() functions to perform this lookup without VBA. That could also help, just because VBA requires a certain overhead that can contribute to poor performance. I guess we are just working from the OP's starting place.