Davidharter90
New Member
- Joined
- Jun 27, 2013
- Messages
- 6
So a little background. I have a worksheet with about 600k rows of data (oil and gas wells). Each well has its own unique 14 digit identifier in column A and once a year i update our database with the new wells. Before copying over the new wells into the database, the code is suppose to first find and delete the wells already in the database which is where my problem arises.
I dim all my workbooks and worksheets outside of the sub routine btw
Here's the code:
When i F8 or F5 (with stops) the code works perfectly fine and does exactly what i want it to do.
1) Grabs the existing well number from the other worksheet tab
2) Finds the well number in the database tab
3) Deletes the well from the database tab
4) Clears the well from the other worksheet tab and moves on to the next well number
When I just run the code as normal, the Find function stops working and returns chkfind=nothing even though the well number exists. I even it did it manually by ctrl+F and that finds the value just fine so I know the well is there but for some reason the VBA find wont return it. I can't figure this out so I'm hoping someone on here can.
I dim all my workbooks and worksheets outside of the sub routine btw
Here's the code:
Code:
Sub chkduplicates()
Dim chkfind As Range
Set wbook = ThisWorkbook
Set chsheet = wbook.Worksheets("Check")
Set ncsheet = wbook.Worksheets("NewCasing")
Set fsheet = wbook.Worksheets("Format")
lrownc = Range("A1").End(xlDown).Row
lcolnc = Range("A1").End(xlToRight).Column
chsheet.Select
lrowch = Range("A1").End(xlDown).Row
Range(Cells(3, 2), Cells(lrowch, 2)).Clear
chsheet.Range(Cells(3, 1), Cells(lrowch, 1)).Copy
chsheet.Cells(3, 2).Select
ActiveSheet.Paste
Cells(3, 2).Select
Application.CutCopyMode = False
ncsheet.Select
i = 0
j = 0
k = 0
10: Cells(2 + j, 1).Select
For i = k To lrowch
chkval = chsheet.Cells(3 + i, 2).Value
Set chkfind = Cells.Find(What:=chkval, After:=Cells(1, 1), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Maybe it's running to fast...I don't know so lets try a wait...
Application.Wait Now + #12:00:02 AM#
If chkfind Is Nothing Then
chsheet.Cells(3 + i, 2).Clear
GoTo 11
Else
delrow = chkfind.Row + 1
chkval2 = chkfind.Value
End If
If chkval = chkval2 Then
Do Until chkval <> chkval2
chkval2 = Cells(delrow - 1 + j, 1).Value
j = j + 1
Loop
delrow2 = Cells(delrow - 1 + j, 1).Row - 2
Range(Cells(delrow - 1, 1), Cells(delrow2, lcolnc)).Select
If delrow <= 2 Or delrow2 <= 2 Then
k = i
j = 0
lrownc = Range("A1").End(xlDown).Row
GoTo 10
End If
Selection.Delete
lrownc = Range("A1").End(xlDown).Row
chsheet.Cells(3 + i, 2).Clear
delrow = ""
delrow2 = ""
Set chkfind = Nothing
End If
11: j = 0
Cells(2 + j, 1).Select
chkval2 = ""
Next i
Cells(1, 1).Select
chsheet.Select
Range(Cells(3, 2), Cells(lrowch, 2)).Clear
ncsheet.Select
End SubWhen i F8 or F5 (with stops) the code works perfectly fine and does exactly what i want it to do.
1) Grabs the existing well number from the other worksheet tab
2) Finds the well number in the database tab
3) Deletes the well from the database tab
4) Clears the well from the other worksheet tab and moves on to the next well number
When I just run the code as normal, the Find function stops working and returns chkfind=nothing even though the well number exists. I even it did it manually by ctrl+F and that finds the value just fine so I know the well is there but for some reason the VBA find wont return it. I can't figure this out so I'm hoping someone on here can.
