Okay, should be a quick fix, but my bug eyes are tired of looking at this. I need to find a number I know is in column r:t. I have attached the first code that looks for something in column a:e and it works fine. I have tried for 4 days now to modify it to work with column r:t and looking for a number (a phone number) . Can't get it to work. Please help. What am I missing?
This one works:
Dim b As Range, FoundCell As Range, LastCell As Range, lastrow As Range
Dim ws1 As Worksheet
Dim FirstAddr As String
Dim lCount As Long
Set ws1 = ThisWorkbook.ActiveSheet
Set ws2 = ThisWorkbook.Sheets("Company")
Set b = ws2.Range("a2:e65000")
ws1.Unprotect "master32"
ws2.Visible = True
Application.ScreenUpdating = False
a = InputBox("What is the Company's Name you are adding?", "Company")
If a = "" Or a = False Then
ws1.Range("a37").Select
ws1.Protect "master32"
ws2.Visible = False
Exit Sub
End If
With b
Set LastCell = .Cells(.Cells.Count)
End With
lCount = 0
Set FoundCell = b.Find(what:=a, LOOKAT:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
lCount = 1
End If
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
ws1.Range("a3000:a3029").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
Set FoundCell = b.FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
lCount = lCount + 1
Loop
This one doesn't:
Dim lastrow As Long
Dim gg As Range, b As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim FoundCell As Range
Dim FirstAddr As String
Set ws1 = ThisWorkbook.ActiveSheet
Set gg = ws1.Range("ab2997")
ws1.Unprotect "master32"
Application.ScreenUpdating = False
If gg = "IACOMP1" Then
Set ws2 = ThisWorkbook.Sheets("IA Company")
ws2.Visible = True
Set b = ws2.Range("R2:T65000")
lCount = 0
Set FoundCell = b.Find(what:=ws1.Range("R2998"), LOOKAT:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
lCount = 1
End If
Do Until FoundCell Is Nothing
MsgBox lCount
'FoundCell.EntireRow.Copy
'ws1.Range("a3000:a3029").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
Set FoundCell = b.FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
lCount = lCount + 1
Loop
the lcount variable is strictly me testing it.
Foundcell consistently finds "nothing"!?!?!?!?!?
Clearly I am missing something.
Thank you.
Bookmarks