Macros slow in process

[ekbana]

Dear frenz,

i need a solution to fasten the process of my macro, its taking almost 1 hour to complete the process.....the macro as below

Option Explicit

Public Function MatchDigits(LookupValue As Range, LookupRange As Range) As String

Dim Cell As Range
Dim NewNum As String
Dim TempNum As String
Dim Num As Long
Dim i As Long
Dim j As Long

For Each Cell In LookupRange
Num = Len(Cell) - (Len(LookupValue) - 1)
For i = 1 To Num
NewNum = Mid(Cell, i, Len(LookupValue))
TempNum = NewNum
For j = 1 To Len(LookupValue)
TempNum = Replace(TempNum, Mid(LookupValue, j, 1), "", , 1)
Next j
If Len(TempNum) = 0 Then
MatchDigits = NewNum
Exit Function
End If
Next i
Next Cell

MatchDigits = 0

End Function


there is 7 columns and 1000 rows of data, the function of this macro is to match the digits, and the process using this method is really too long to process....is there is shortcut or a formula that is able to cut the time of processing?

pls do really help. thanks in advance

 

[sous2817]

At the start you can turn off screen updating and calculations and at the end turn them back on again, that will help.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


{Your Macro Here}

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Also, if you can explain in words what your macro is doing, that would help as well.

 

[mirabeau]

You seem to have a triple loop in there.

That's a loop inside a loop inside a loop, i.e. looping on j which loops inside the i loop which loops inside the each cell loop. This is asking your computer to carry out a maybe very large number of calculations. Are they all necessary??

This sort of looping can get very slow. If unavoidable then so be it, but often it can be improved on. You do have an Exit Function from inside the i loop, but how much this reduces the looping (even assuming it's otherwise working properly) it's hard to tell without running some test data.

It helps a reader of your post if you use code tags. That is, if you include code in your post to put ["code"] (without the quotation marks) immediately before the code but if your post, and [/code] where the code ends.

In this bit of your code

For j = 1 To Len(LookupValue)
TempNum = Replace(TempNum, Mid(LookupValue, j, 1), "", , 1)
Next j
If Len(TempNum) = 0 Then
MatchDigits = NewNum

you seem to loop with j to produce Len(LookupValue) values of TempNum, and then only make use of the last such value.

What's the purpose of this loop? Why don't you just calculate the last value directly?

If you'd like to post a small sample of what your data look like at start, and what you want the end result to look like, then it's possible you may get several suggestions as to improvements in speed.

 

[ekbana]

You seem to have a triple loop in there.

That's a loop inside a loop inside a loop, i.e. looping on j which loops inside the i loop which loops inside the each cell loop. This is asking your computer to carry out a maybe very large number of calculations. Are they all necessary??

This sort of looping can get very slow. If unavoidable then so be it, but often it can be improved on. You do have an Exit Function from inside the i loop, but how much this reduces the looping (even assuming it's otherwise working properly) it's hard to tell without running some test data.

It helps a reader of your post if you use code tags. That is, if you include code in your post to put ["code"] (without the quotation marks) immediately before the code but if your post, and [/code] where the code ends.

In this bit of your code

For j = 1 To Len(LookupValue)
TempNum = Replace(TempNum, Mid(LookupValue, j, 1), "", , 1)
Next j
If Len(TempNum) = 0 Then
MatchDigits = NewNum

you seem to loop with j to produce Len(LookupValue) values of TempNum, and then only make use of the last such value.

What's the purpose of this loop? Why don't you just calculate the last value directly?

If you'd like to post a small sample of what your data look like at start, and what you want the end result to look like, then it's possible you may get several suggestions as to improvements in speed.

hi,
purpose of this formula as per below example

i want to match the following cell a n cell b to derive result in cell c

a b c
12321 41242
53635 56454
64542 56244 56244

 

[sous2817]

can you explain in words how you got your results?

 

[ekbana]

can you explain in words how you got your results?

hi,
im sorry if im really bad in explaining stuff.... i need to match 2 columns of datas by digits and give result at cell c
but this is how it works
example, ive got 3 columns (not neccessary 3, but can b more), and 4 rows of datas,
cell a and b contains data, cell A must match cell B and result at cell C

a b c
1: 1234 4356 N/A (no match)
2: 3432 4332 4332 (match all the 4 digits)
3: 4215 1234 1234 (matches row cell 1 and highlights by colour the row that matches if possible)
4: 4267 7642 7642 (match all the 4 digits)

the formula which i am currently using working flawlessly, but d only problem is its taking hours to calculate.....can some1 help me to give me a faster macros result
tq

 

[mirabeau]

cell a and b contains data, cell A must match cell B and result at cell C

a b c
1: 1234 4356 N/A (no match)
2: 3432 4332 4332 (match all the 4 digits)
3: 4215 1234 1234 (matches row cell 1 and highlights by colour the row that matches if possible)
4: 4267 7642 7642 (match all the 4 digits)

After this explanation I'm still unable to see what you want. I'm sorry but I can't be of any help.