TFCJamieFay
Active Member
- Joined
- Oct 3, 2007
- Messages
- 480
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
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