CODEWITHSUNDEEP
excelvba
Alpan67
Alpan67

Reputation: 105

search numbers in vba excel

I am looking to search a couple of numbers together (National lottery); i have to look in 5 cloumns each time and there are 90 numbers. The possibilities are 1.2 1.3 1.4 ... 89.90 and i have 4005 combinations. The algorythm works well but the time for the search is absolutely impossible to manage. Is there a possibility to speed up the research ?

For amb = 2 To 4006

    primo = Foglio3.Cells(amb, 1)
    secondo = Foglio3.Cells(amb, 2)

    ritardo = 0

    For cont = 8618 To 2 Step -1

        est1 = Foglio2.Cells(cont, 2)
        est2 = Foglio2.Cells(cont, 3)
        est3 = Foglio2.Cells(cont, 4)
        est4 = Foglio2.Cells(cont, 5)
        est5 = Foglio2.Cells(cont, 6)

        If (primo = est1) Or (primo = est2) Or (primo = est3) Or (primo = est4) Or (primo = est5) Then
            If (secondo = est1) Or (secondo = est2) Or (secondo = est3) Or (secondo = est4) Or (secondo = est5) Then
                Foglio3.Cells(amb, 3) = ritardo     '3 = nazionale
                Exit For
            End If

        End If

        ritardo = ritardo + 1

    Next cont

Next amb

Upvotes: 0

Views: 102

Answers (1)

InContext
InContext

Reputation: 2501

first step is to stop using the sheet and VBA in each loop. So store the data in an array and then iterate over the in memory array. Change variants to suite your data types on the sheet if required. NOTE: the Range references for Foglio2 and Foglio3 will need to be changed to suite your dataset.

Dim foglio2() As Variant, foglio3() As Variant
Dim i As Double

Dim primo As Variant, secondo As Variant
Dim est1 As Variant, est As Variant, est3 As Variant, est4 As Variant, est5 As Variant

Dim resultArray() As Variant

foglio3 = Foglio3.Range("A2").CurrentRegion
foglio2 = Foglio2.Range("A2").CurrentRegion

For i = 2 To UBound(foglio2) ' maybe change to 4006?

    primo = foglio2(1, 1)
    secondo = foglio2(1, 2)

    ' change J to 8616?
    For j = UBound(foglio3) To 2 Step -1

        est1 = foglio3(j, 2)
        est2 = foglio3(j, 3)
        est3 = foglio3(j, 4)
        est4 = foglio3(j, 5)
        est5 = foglio3(j, 6)

        ReDim Preserve resultArray(i)
        If (primo = est1) Or (primo = est2) Or (primo = est3) Or (primo = est4) Or (primo = est5) Then
            If (secondo = est1) Or (secondo = est2) Or (secondo = est3) Or (secondo = est4) Or (secondo = est5) Then

                resultArray(i) = ritardo     '3 = nazionale
                Exit For
            End If

        Else
            resultArray(i) = vbNullString
        End If

        ritardo = ritardo + 1


    Next j

Next i

Foglio3.Cells(2, 3).Resize(UBound(resultArray), 1) = resultArray

Upvotes: 1

Related Questions