How to have quicker search method?
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.
Code:
'/////////////////////////////////////////////////////////////////
' 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
Re: How to have quicker search method?
My previous capture the string in it also very slow, is there anyway to improve it? THanks.
Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'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
Re: How to have quicker search method?
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.
Re: How to have quicker search method?
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 !
Re: How to have quicker search method?
This is the syntax of the function.
VB Code:
Range("Cell1", "Cell2").Find "What", "After", "LookIn", "LookIn", "SearchOrder", "SearchDirection", "MatchCase", "MatchByte", "SearchFormat"
Re: How to have quicker search method?
What should be in "What", "After", "LookIn", "LookIN", "SearchOrder", "SearchDirection", "MatchCase", "MatchByte", "SearchFormat" ?
Can you give me an example? and what it returns ?
Re: How to have quicker search method?
VB Code:
Range(Cells(1, 1), Cells(1,50)).Find "Test"
Is all you basically need unless you need an advanced find with all the options.
Re: How to have quicker search method?
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 ?
Re: How to have quicker search method?
Quote:
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
Quote:
a = Range(Cells(1, 1), Cells(1, 50)).Find("Test")
a will = "Test" if found
pete
Re: How to have quicker search method?
If one cell contain this data: " - Daniel -Alvin -David "
a = Range(Cells(1, 1), Cells(1, 50)).Find("Daniel")
What will it returns?
