"Error 91"

[TFCJamieFay]

Afternoon all,

I'm trying to write a piece of code that will sort numbers depending the first digit but I keep getting "Run-time error '91' - Object variable or With block variable not set" on the line:

"mCell = ActiveCell.Offset(columnoffset:=2, rowoffset:=0).Value"

But when I hover my mouse over the line of code it gives the correct value, so it seems to be finding the correct cell and extracting the correct value. I'm confused!!

My code is below:

Sub test2()

Dim MTN, BTN As String
Dim bCell, mCell As Object

lastrow = Range("A" & Rows.Count).End(xlUp).Row

Range("D2:D" & lastrow).Select

For Each cell In Selection
cell.Select
mCell = ActiveCell.Offset(columnoffset:=2, rowoffset:=0).Value
bCell = ActiveCell.Offset(columnoffset:=1, rowoffset:=0).Value
MTN = mCell.Value
BTN = bCell.Value
Select Case Left(cell, 1)
Case "7"
If MTN = "" Then
mCell.Value = cell.Value
cell.Value = ""
End If
Case "1", "2"
If Left(bCell, 1) = "7" Then
mCell.Value = bCell.Value
bCell.Value = ""
End If
Case Else
If cell.Value = "" And Left(bCell, 1) = "1" Or Left(bCell, 1) = "2" Then
cell.Value = bCell.Value
End If
End Select
Next

End Sub

Many thanks,

Jamie

 

[Norie]

Jamie

Do you really need to select?

For Each cell In Range("D2:D" & lastrow)
       mCell = cell.Offset(columnoffset:=2, rowoffset:=0).Value
       bCell = cell.Offset(columnoffset:=1, rowoffset:=0).Value
' etc

 

[RoryA]

You declare mCell as an Object but then try and pass it the value of a cell, which makes no sense. I suspect you wanted:

Set mCell = ActiveCell.Offset(columnoffset:=2, rowoffset:=0)

also, you should be aware that if you use this:

Dim bCell, mCell As Object

you have only declared mCell as an Object. bCell is the default Variant type because you did not specify its type. You should use:

Dim bCell as Object, mCell As Object

and similarly for your string declarations.

 

[TFCJamieFay]

I don't know of any other way to do it without selecting the cell in question.

 

[TFCJamieFay]

Thanks Rory, you seem to have steered me in the right direction.

 

[drebcr]

Hi there,

Wasn't sure where to put this as this is my 1st post. Didn't think I needed to create a new thread as this is a part of my problem anyway.

I am trying to do a loop with what seems to be a very simple piece of code and I keep getting the runtime error 91.

I have this piece which runs fine until the end then the part in red is what's highlighted for debugging.

Sub test()                
 Do
  With Application.FindFormat.Font
     .Name = "Arial"
     .FontStyle = "Regular"
     .Size = 8
     .ColorIndex = xlAutomatic
   End With
        Range("A4").Select
           Cells.Find(what:="$", after:=ActiveCell, LookIn:=xlFormulas, lookat:= _
             xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
                 , searchformat:=True).Activate
            Range(Selection, Selection.End(xlDown)).Select
            Selection.EntireRow.Select
            Selection.Delete Shift:=xlUp
Loop
                
End Sub

The amount of times it needs to run will differ each time so the other thing I was trying to figure out is how to add a MsgBox at the end to let me know there's no more to find and delete and to obviously exit the loop. The code ran fine before I tried to put it into a loop!!!!!

I hope this makes sense.

Thanks for any help with this

Darrin