error '91' object variable or with block variable not set

[cissilya]

Sub makefaxlist()
Application.EnableEvents = False
Application.ScreenUpdating = False
current_book = ActiveWorkbook.Name
Worksheets("sublist").Activate
current_path = Sheets("info").Cells(10, 5).Value
Do
faxlistname = InputBox("Name of Fax Listing:", "Faxlist Name", "faxlist")
If faxlistname = "" Then Exit Sub
dupfaxlist = False
If Not Range("faxlist").Find(faxlistname, Lookat:=xlWhole) Is Nothing Then
dupfaxlist = True
MsgBox ("A fax listing with that name already exists. Enter a new name.")
End If
Loop Until dupfaxlist = False
Range("faxlist").Cells(Range("faxlist").Count).Offset(0, 1).Value = faxlistname
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "faxlist"
Sheets("sublist").Select
Range(Cells.Find("Name").Address).Select
'Range(Selection, Selection.SpecialCells(xlLastCell)).Select
Sheets("sublist").UsedRange.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("faxlist").Paste

Application.CutCopyMode = False
Sheets("faxlist").Select
Cells(1, 1).Select
On Error Resume Next
Cells.Find("fax").EntireColumn.Cut
Columns("A:A").Insert Shift:=xlToRight
Cells.Find("name").EntireColumn.Cut
Columns("B:b").Insert Shift:=xlToRight
Cells.Find("contact").EntireColumn.Cut
Columns("c:c").Insert Shift:=xlToRight
Range("d1", Range("A1").SpecialCells(xlLastCell)).EntireColumn.Delete
Cells.Select
Selection.Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows(1).Insert
Cells(1, 1).Select
x = 2
'For y = 2 To Range("a1").SpecialCells(xlLastCell).Row
For y = 1 To Sheets("faxlist").UsedRange.Rows.Count
If Cells(x, 1).Value = Cells(x - 1, 1).Value Or IsEmpty(Cells(x, 1)) Then
Rows(x).Delete
Else
x = x + 1
End If
Next
Cells.Select
Cells.EntireColumn.AutoFit
Cells(1, 1).Select
Workbooks(current_book).Sheets("faxlist").Copy
ActiveWorkbook.SaveAs Filename:=current_path + "\" + faxlistname + ".csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveWindow.Close savechanges:=False
Application.DisplayAlerts = False
Workbooks(current_book).Sheets("faxlist").Delete
Application.DisplayAlerts = True
Workbooks(current_book).Sheets("sublist").Activate
ActiveSheet.Cells(3, 1).Activate
MsgBox ("Faxlist has been created.")
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Loomah]

Hi
We'll need a little more info than you've given here!

Where is the offending line of code?

On first inspection - just stepping through the code ignoring errors - the find method fails (error 91) every time. Does the text you are looking for exist in the sheet in which you are looking for it?

On another point, why look for it? Why not just activate the cell? Is it not static?

On another point there is very rarely a requirement to activate/select objects (such as cells, sheets etc.) in (Excel) VBA!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[cissilya]

the error code im getting is the 91 on the line of code
Range(Cells.Find("Name").Address).Select.
it is supposed to create a faxlist that gets put in the root of the project folder, and for some reason it is not doing it.
This is a project i took over from someone, and they did not leave all the complete information when they left. so im trying to piece together the puzzel.
Please let me know what you think and what else you need.

 

[Loomah]

Hi again!
The error is happening where I thought it might. Please go back over my other questions though, particularly: What precisely is "Name"?

If it is text, does it actually exist on the sheet?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[firefytr]

Not sure what you're trying to do, but you have some things to work out in your code ...


Sub makefaxlist()

Dim wbSub As Workbook, wbCurr As Workbook
Dim wsCurr As Worksheet, rngFind As Range, rngName As Range, LastCell As Range
Dim Current_Book As String, Current_Path As String, FaxListName As String
Dim DupFaxList As Boolean, x As Long, y As Long

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Set wbCurr = ActiveWorkbook
Set wsCurr = wbCurr.Sheets("sublist")
Current_Book = wbCurr.Name
Current_Path = Sheets("info").Cells(10, 5).Value

Do Until DupFaxList = False
FaxListName = InputBox("Name of Fax Listing:", "Faxlist Name", "faxlist")
If FaxListName = "" Then Exit Sub
DupFaxList = False
Set rngFind = Range("faxlist").Find(FaxListName, Lookat:=xlWhole) 'where is this at?!?!
If Not rngFind Is Nothing Then
DupFaxList = True
MsgBox ("A fax listing with that name already exists. Enter a new name.")
End If
Loop

Range("faxlist").Cells(Range("faxlist").Count).Offset(0, 1).Value = FaxListName 'where is this at?!?!
Sheets.Add after:=Sheets(Sheets.Count) 'where is this at?!?!
Sheets(Sheets.Count).Name = "faxlist" 'where is this at?!?!
Sheets("sublist").Select 'where is this at?!?!

Set LastCell = Cells.Find("*", after:=Cells(1, 1), searchdirection:=xlPrevious) 'where is this at?!?!

Set rngName = Cells.Find("Name")
If Not rngName Is Nothing Then
Range(rngName, LastCell).Copy
Sheets("faxlist").Range("A1").PasteSpecial xlPasteValues
End If

'do you really need this here???...
Sheets("faxlist").Select
Cells(1, 1).Select

On Error Resume Next
Cells.Find("fax").EntireColumn.Cut Columns("A:A").Insert(Shift:=xlToRight)
Cells.Find("name").EntireColumn.Cut Columns("B:B").Insert(Shift:=xlToRight)
Cells.Find("contact").EntireColumn.Cut Columns("c:c").Insert(Shift:=xlToRight)
Range("d1", Range("A1").SpecialCells(xlLastCell)).EntireColumn.Delete
Cells.Sort Key1:=Range("a1"), Order1:=xlAscending, Header:=xlYes
Rows(1).Insert
Cells(1, 1).Select
x = 2
For y = Sheets("faxlist").UsedRange.Rows.Count To 1 Step -1
If Cells(x, 1).Value = Cells(x - 1, 1).Value Or IsEmpty(Cells(x, 1)) Then
Rows(x).Delete
Else
x = x + 1
End If
Next

Cells.EntireColumn.AutoFit
Cells(1, 1).Select
Workbooks(Current_Book).Sheets("faxlist").Copy
wbSub.SaveAs Current_Path + "\" + FaxListName + ".csv", FileFormat:=xlCSV

wbSub.Close False
wbCurr.Sheets("faxlist").Delete
wbCurr.Sheets("sublist").Activate
wbCurr.Sheets("sublist").Cells(3, 1).Activate
MsgBox ("Faxlist has been created.")

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Those are some very, very minor adjustments. Everywhere that you see Cells or Range, there needs to be an explicit reference behind it for a worksheet and each worksheet should be referenced to a workbook.

-----------
Regards,
Zack Barresse