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

[cissilya]

i get the above message on the following line of code

Range(Cells.Find("Name").Address).Select

does anyone have any idea why this would happen?

 

[Sheco]

if Cells.Find("Name") returns nothing then you'd get the error trying to read the .Address property of nothing... same thing trying to read the .Select property if your Range is nothing.

 

[cissilya]

im taking this over from someone old, so im not sure i have a good understanding of it yet. so the "name" should refere to ?

 

[Sheco]

This is just a guess but it looks like you've got an object named Cells.

Your Cells object is probably some sort of collection/array that holds a bunch of Cell objects.

The Cells object has a method named Find.

You pass a string to the Find method of the Cells object, and the Find method returns an object... probably a single Cell object from the collection.

The string being passed is "Name"

The object returned by the Find method is assumed to have a property named Address... if the Find method DOES NOT return an object then you'll get this error 91.

OK, but lets assume for a minute that the Find method correctly returns an object, but that object does have a property named Address... in this case you would get an error but it would say something like "invalid property or method not found" or somesuch.

Now, lets assume that everything goes as planned and the Find method of the Cells object returns an object with a property named Address... Then the value of the Address property is passed to the Range() method. This Range method must then return an object that has a method named Select.

Follow?

 

[Sheco]

whoops, i should have previewed that before posting... it has an omission that changes the meaning.

It should have been:
OK, but lets assume for a minute that the Find method correctly returns an object, but that object does [red]NOT[/red] have a property named Address... in this case you would get an error but it would say something like "invalid property or method not found" or somesuch

 

[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

 

[johnwm]

This is looking like an Excel VBA question, not a VB problem. Suggest you read faq222-2244 to get guidance on forum usage, then repost in forum707

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[chiph]

Check your inner expressions for null.

You'd do something like this (this is not real code):

Cell myCell = Cells.Find("Name")
if (myCell <> null)
{
   Address myAddress = myCell.Address
   if (myAddress <> null)
   {
      Range myRange = Range(myAddress)
      if (myRange <> null)
      {
         myRange.Select
      }
   }
}

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first