My current code works great for "column A" (code attached), but not so great for "column Y" (code also attached). The error is for run time error 1004 (copy and paste not same size). Column A will be empty for this code so it's not an option. Also there is other data, not related above and below my range of 3000-3111 so......... I can't seem to get it to work properly. Any help is greatly appreciated.
this one works
Sub IA_Company_2()
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("Contacts") ' CHANGE THIS TO THE NEW IA COMPANY LOGBOOK
Set b = ws2.Range("a8:e9999")
ws1.Unprotect "master32"
Application.ScreenUpdating = False
a = InputBox("What is the IA Company's Name you are adding? HINT: You can type RHI to look for Rhino Claims.", "IA Company", Rhi)
If a = "" Or a = False Then
GoTo 100
End If
With b
Set LastCell = .Cells(.Cells.Count)
End With
lCount = 1
Set FoundCell = b.Find(what:=a, LookAt:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
ws1.Range("a3000:a3111").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
Set FoundCell = b.FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
lCount = lCount + 1
Loop
If lCount > 30 Then
MsgBox ("Too many matches......Try narrowing your search. Be more specific")
GoTo 100
End If
ws1.Range("ab2998") = "IACOMP2"
100
ws1.Protect "master32"
Application.ScreenUpdating = True
ws1.Range("a3013").Select
This one doesn't
Sub IA_Name_1()
ActiveSheet.Unprotect "master32"
Dim b As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim FoundCell As Range
Dim LastCell As Range
Dim FirstAddr As String
Set ws1 = ThisWorkbook.ActiveSheet
Set ws2 = ThisWorkbook.Sheets("Contacts") ' CHANGE THIS TO THE NEW IA COMPANY LOGBOOK
Set b = ws2.Range("y20000:aa39999")
Application.ScreenUpdating = False
a = InputBox("What is the IA's name you are adding? HINT: You can type ROB to look for Robert James.", "IA Name", Rob)
If a = "" Or a = False Then
GoTo 100
End If
With b
Set LastCell = .Cells(.Cells.Count)
End With
lCount = 1
Set FoundCell = b.Find(what:=a, LookAt:=xlPart, LookIn:=xlValues, SearchDirection:=xlNext)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
End If
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Copy
ws1.Range("y3000:aa3111").SpecialCells(xlCellTypeBlanks).Cells(1).PasteSpecial
Set FoundCell = b.FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddr Then
Exit Do
End If
lCount = lCount + 1
Loop
If lCount > 30 Then
MsgBox ("Too many matches......Try narrowing your search. Be more specific")
Exit Sub
End If
ws1.Range("ab2998") = "IANAME1"
100
ws1.Protect "master32"
Application.ScreenUpdating = True
ws1.Range("a3013").Select
End Sub
Bookmarks