How to have quicker search method?

[danielngoo]

My current searching takes a lot of time as the following code, is there any method to improve search codes especially looking into cells in excel?

I found that i use Ctrl-F , even loop fo find to the end of IV65536 column from first column, it takes less than 2 seconds, i was amazed by the speed of their searching, so i am thinking of why my looping and searching is so slow.

'/////////////////////////////////////////////////////////////////
' Update Value in Demand-PM
'////////////////////////////////////////////////////////////////


Dim rMonth As String
rMonth = Mid(Ce11.address, 2, 1)

Dim count1 As Integer

For count1 = 6 To 39

    If Range("'Demand - PM'!C" & count1).Value = string1 And string1FTE = "F" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 1
        ''msgbox "String1FTE F deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string1 And string1FTE = "P" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.5
        'msgbox "String1FTE P deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string1 And string1FTE = "Q" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.25
        'msgbox "String1FTE Q deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string2 And string2FTE = "F" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 1
        'msgbox "String2FTE F deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string2 And string2FTE = "P" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.5
        'msgbox "String2FTE P deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string2 And string2FTE = "Q" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.25
        'msgbox "String2FTE Q deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string3 And string3FTE = "F" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 1
        'msgbox "String3FTE F deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string3 And string3FTE = "P" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.5
        'msgbox "String3FTE P deleted"
    End If
    
    
    If Range("'Demand - PM'!C" & count1).Value = string3 And string3FTE = "Q" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.25
        'msgbox "String3FTE Q deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string4 And string4FTE = "F" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 1
        'msgbox "String4FTE F deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string4 And string4FTE = "P" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.5
        'msgbox "String4FTE P deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string4 And string4FTE = "Q" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.25
        'msgbox "String4FTE Q deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string5 And string5FTE = "F" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 1
        'msgbox "String5FTE F deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string5 And string5FTE = "P" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.5
        'msgbox "String5FTE P deleted"
    End If
    
    If Range("'Demand - PM'!C" & count1).Value = string5 And string5FTE = "Q" Then
        Range("'Demand - PM'!" & rMonth & count1).Value = Range("'Demand - PM'!" & rMonth & count1).Value - 0.25
        'msgbox "String5FTE Q deleted"
    End If
    
Next

Next


'////////////////////////////////////////////////////////////////////
' Delete in PM Allocation
'////////////////////////////////////////////////////////////////////

Dim countPMAllocation, countPMAllocation2 As Integer
Dim delUpdate As Integer
Dim rmonth1 As String
Dim RangeAddress
Dim RangeAddressInt As Integer
    For countPMAllocation = 6 To 50
    'MsgBox ("Come to delete PM Allocation Condition")
        For Each Project In Worksheets("PM Allocation").Range("B" & PrjDelCmb.Value, "O" & PrjDelCmb.Value).Cells
        Project.Value = ""
        'MsgBox ("Delete successful")
    Next
    Next
    
    For countPMAllocation2 = 6 To 50
    
    If Range("B" & countPMAllocation2).Value = "" Then
       ' MsgBox ("Come to update PM Allocation")
        For Each UpdateDel In Worksheets("PM Allocation").Range("B" & countPMAllocation2, "O" & countPMAllocation2).Cells
         rmonth1 = Mid(UpdateDel.address, 2, 1)
         Range(rmonth1 & countPMAllocation2).Value = Range(rmonth1 & countPMAllocation2 + 1).Value
         Range(rmonth1 & countPMAllocation2 + 1).Value = ""
        ' MsgBox ("Come to moving PM Allocation")
        Next
    End If
    
    Next
    



'////////////////////////////////////////////////////////////////////////
' Update in Demand- Project Sheet
'////////////////////////////////////////////////////////////////////////


Dim countDemandProject, countDemandProject2 As Integer
Dim rMonth2 As String

    
    'MsgBox ("Come to delete PM Allocation Condition")
        For Each Project In Worksheets("Demand - Project").Range("B" & PrjDelCmb.Value, "O" & PrjDelCmb.Value).Cells
        Project.Value = ""
        'MsgBox ("Delete successful")
       Next
    
    For countDemandProject2 = 6 To 50
    
    If Range("'Demand - Project'!B" & countDemandProject2).Value = "" Then
       ' MsgBox ("Come to update PM Allocation")
        For Each UpdateDelProject In Worksheets("Demand - Project").Range("B" & countDemandProject2, "O" & countDemandProject2).Cells
         rMonth2 = Mid(UpdateDelProject.address, 2, 1)
        Range("'Demand - Project'!" & rMonth2 & countDemandProject2).Value = Range("'Demand - Project'!" & rMonth2 & countDemandProject2 + 1).Value
         Range("'Demand - Project'!" & rMonth2 & countDemandProject2 + 1).Value = ""
        ' MsgBox ("Come to moving PM Allocation")
        Next
    End If
    
    Next
    

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Delete project in hidden flow
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For countHidden = 1 To 50
 
    If Range("'hidden'!H" & countHidden).Value = PrjDelCmb.Value Then
        Range("'hidden'!G" & countHidden).Value = ""
        Range("'hidden'!H" & countHidden).Value = ""
    End If
    
    
    For countHidden1 = 1 To 50
        If Range("'hidden'!G" & countHidden1).Value = "" Then
            Range("'hidden'!G" & countHidden1).Value = Range("'hidden'!G" & countHidden1 + 1).Value
            Range("'hidden'!H" & countHidden1).Value = Range("'hidden'!H" & countHidden1 + 1).Value
            Range("'hidden'!H" & countHidden1).Value = Range("'hidden'!H" & countHidden1 + 1).Value - 1
            Range("'hidden'!G" & countHidden1 + 1).Value = ""
            Range("'hidden'!H" & countHidden1 + 1).Value = ""
            
            
        End If
    Next
Next

[danielngoo]

My previous capture the string in it also very slow, is there anyway to improve it? THanks.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Delete in Demand-PM sheet and Update PM Value
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim delCell As String
Dim delStart, delEnd As Integer
Dim string1 As String
Dim string2 As String
Dim string3 As String
Dim string4 As String
Dim string5 As String
Dim delFTEStart, delFTEEnd As Integer
Dim string1FTE As String
Dim string2FTE As String
Dim string3FTE As String
Dim string4FTE As String
Dim string5FTE As String


If PrjDelCmb.Value = "" Then
MsgBox "Please Choose Project "
Exit Sub
End If

For Each Ce11 In Worksheets("PM Allocation").Range("D" & PrjDelCmb.Value, "O" & PrjDelCmb.Value).Cells
string1 = ""
string2 = ""
string3 = ""
string4 = ""
string5 = ""

string1FTE = ""
string2FTE = ""
string3FTE = ""
string4FTE = ""
string5FTE = ""
        
'///////////////////////////////////////////////////////////////////////////////////////////
'Capture PM in a cell
'//////////////////////////////////////////////////////////////////////////////////////////
                       
        delCell = Ce11.Value
        delStart = 1
        
        Dim w As Integer
        If delStart = 1 Then
            w = InStr(1, delCell, "-", vbTextCompare) + 1
            delStart = w
            'MsgBox ("delStart1: " & delEnd)
        End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim y As Integer

        If delStart > 2 Then
            y = InStr(delStart, delCell, "-", vbTextCompare) + 1
            delStart = y
            'MsgBox ("delStart2: " & delEnd)
        End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim z As Integer
        If delStart > 2 Then
            z = InStr(delStart, delCell, "-", vbTextCompare) + 1
            delStart = z
            'MsgBox ("delStart3: " & delStart)
        End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim z1 As Integer
        If delStart > 2 Then
            z1 = InStr(delStart, delCell, "-", vbTextCompare) + 1
            delStart = z1
            'MsgBox ("delStart4: " & delStart)
        End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim z2 As Integer
        If delStart > 2 Then
            z2 = InStr(delStart, delCell, "-", vbTextCompare) + 1
            'MsgBox ("delStart5: " & delStart)
        End If
        
        
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Capture Cell End Character
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
delEnd = 1

        Dim a1 As Integer

        If delEnd = 1 Then
            a1 = InStr(1, delCell, "(", vbTextCompare) + 1
            delEnd = a1
            'MsgBox ("delEnd : " & delEnd)
        End If
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim a As Integer
        If delEnd > 2 Then
            a = InStr(delEnd, delCell, "(", vbTextCompare) + 1
            delEnd = a
            'MsgBox ("delEnd second :" & a)
        End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim b As Integer
        If delEnd > 2 Then
            b = InStr(delEnd, delCell, "(", vbTextCompare) + 1
            delEnd = b
            'MsgBox ("delEnd Third :" & b)
        End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim c As Integer
        If delEnd > 2 Then
            c = InStr(delEnd, delCell, "(", vbTextCompare) + 1
            delEnd = c
            'MsgBox ("delEnd Fourth :" & c)
        End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim d As Integer
        If delEnd > 2 Then
            d = InStr(delEnd, delCell, "(", vbTextCompare) + 1
            delEnd = d
            'MsgBox ("delEnd Fifth :" & d)
        End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Capture PM string
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


If a1 - w - 1 > 0 And string1 = "" Then
string1 = Mid(delCell, w + 1, a1 - w - 3)
'MsgBox ("String 1:" & string1)
End If

If a - y - 1 > 0 And string2 = "" Then
string2 = Mid(delCell, y + 1, a - y - 3)
'MsgBox ("String 2:" & string2)
End If

If b - z - 1 > 0 And string3 = "" Then
string3 = Mid(delCell, z + 1, b - z - 3)
'MsgBox ("String 3:" & string3)
End If

If c - z1 - 1 > 0 And string4 = "" Then
string4 = Mid(delCell, z1 + 1, c - z1 - 3)
'MsgBox ("String 4:" & string4)
End If

If d - z2 - 1 > 1 And string5 = "" Then
string5 = Mid(delCell, z2 + 1, d - z2 - 3)
'MsgBox ("String 5:" & string5)
End If



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'End Identifying PM in cell
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'///////////////////////////////////////////////////////////////////////////////////////
'Start Identifying FTE in cell
'///////////////////////////////////////////////////////////////////////////////////////

delFTEStart = 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim wFTE As Integer

        If delFTEStart = 1 Then
            wFTE = InStr(1, delCell, "(", vbTextCompare) + 1
            delFTEStart = wFTE
            'MsgBox ("delFTEStart : " & delFTEStart)
        End If
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim yFTE As Integer
        
        If delFTEStart > 2 Then
            yFTE = InStr(delFTEStart, delCell, "(", vbTextCompare) + 1
            delFTEStart = yFTE
            'MsgBox ("delFTEStart second :" & yFTE)
        End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim zFTE As Integer
    
        If delFTEStart > 2 Then
            zFTE = InStr(delFTEStart, delCell, "(", vbTextCompare) + 1
            delFTEStart = zFTE
            'MsgBox ("delFTEStart Third :" & zFTE)
        End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim z1FTE As Integer
        If delFTEStart > 2 Then
            z1FTE = InStr(delFTEStart, delCell, "(", vbTextCompare) + 1
            'MsgBox ("delFTEStart fourth :" & z1FTE)
            delFTEStart = z1FTE
        End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
        Dim z2FTE As Integer
        If delFTEStart > 2 Then
            z2FTE = InStr(delFTEStart, delCell, "(", vbTextCompare) + 1
            delFTEStart = z2FTE
            'MsgBox ("delFTEStart Fifth :" & z2FTE)
        End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Capture FTE End Character
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

delFTEEnd = 1

        Dim a1FTE As Integer

        If delFTEEnd = 1 Then
            a1FTE = InStr(1, delCell, ")", vbTextCompare) + 1
            delFTEEnd = a1FTE
            'MsgBox ("delFTEEnd : " & delFTEEnd)
        End If
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim aFTE As Integer
        If delFTEEnd > 2 Then
            aFTE = InStr(delFTEEnd, delCell, ")", vbTextCompare) + 1
            delFTEEnd = aFTE
            'MsgBox ("delFTEEnd second :" & aFTE)
        End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim bFTE As Integer
        If delFTEEnd > 2 Then
            bFTE = InStr(delFTEEnd, delCell, ")", vbTextCompare) + 1
            delFTEEnd = bFTE
            'MsgBox ("delFTEEnd Third :" & bFTE)
        End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Dim cFTE As Integer

        If delFTEEnd > 2 Then
            cFTE = InStr(delFTEEnd, delCell, ")", vbTextCompare) + 1
            delFTEEnd = cFTE
            'MsgBox ("delFTEEnd Fourth :" & cFTE)
        End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


        Dim dFTE As Integer
            If delFTEEnd > 2 Then
            dFTE = InStr(delFTEEnd, delCell, ")", vbTextCompare) + 1
            delFTEEnd = dFTE
            'MsgBox ("delFTEEnd Fifth :" & dFTE)
        End If


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Capture FTE string
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If a1FTE - wFTE - 1 > 0 And string1FTE = "" Then
string1FTE = Mid(delCell, wFTE, a1FTE - wFTE - 1)
'MsgBox ("string1 FTE: " & string1FTE)
End If

If aFTE - yFTE - 1 > 0 And string2FTE = "" Then
string2FTE = Mid(delCell, yFTE, aFTE - yFTE - 1)
'MsgBox ("string2 FTE: " & string2FTE)
End If

If bFTE - zFTE - 1 > 0 And string3FTE = "" Then
string3FTE = Mid(delCell, zFTE, bFTE - zFTE - 1)
'MsgBox ("string3 FTE: " & string3FTE)
End If

If cFTE - z1FTE - 1 > 0 And string4FTE = "" Then
string4FTE = Mid(delCell, z1FTE, cFTE - z1FTE - 1)
'MsgBox ("string4 FTE: " & string4FTE)
End If

If dFTE - z2FTE - 1 > 0 And string5FTE = "" Then
string5FTE = Mid(delCell, z2FTE, dFTE - z2FTE - 1)
'MsgBox ("string5 FTE: " & string5FTE)
End If




'///////////////////////////////////////////////////////////////
' End Identifying FTE in cell
'//////////////////////////////////////////////////////////////

'Next

[RobDog888]

Checkout the .Find method for duplicating the Find and Find Next method.

Actually matching the cells in a loop is slower then the .Find for each scenerio.

[danielngoo]

RobDog888,

Thanks for your suggestion, by the way, can you give me some example how i implement the find in perhaps one small part of my current source code? Thank you !

[RobDog888]

This is the syntax of the function.

Range("Cell1", "Cell2").Find "What", "After", "LookIn", "LookIn", "SearchOrder", "SearchDirection", "MatchCase", "MatchByte", "SearchFormat"

[danielngoo]

What should be in "What", "After", "LookIn", "LookIN", "SearchOrder", "SearchDirection", "MatchCase", "MatchByte", "SearchFormat" ?

Can you give me an example? and what it returns ?

[RobDog888]

Range(Cells(1, 1), Cells(1,50)).Find "Test"

Is all you basically need unless you need an advanced find with all the options.

[danielngoo]

By the way, what it returns ?

let's say
Dim a
a= Range(Cells(1, 1), Cells(1,50)).Find "Test"

1. What is the value of a ?
2. IF i want to check the Range from other sheet, what the code will look like ?

[westconn1]

a = Range(Cells(1, 1), Cells(1, 50)).Find("Test").Activate

a will equal true if found and the cell found will be the current cell

a = Range(Cells(1, 1), Cells(1, 50)).Find("Test")

a will = "Test" if found

pete

[danielngoo]

If one cell contain this data: " - Daniel -Alvin -David "

a = Range(Cells(1, 1), Cells(1, 50)).Find("Daniel")

What will it returns?