Exiting loop if value = "Total"!!!

[fawnlemur]

Hi


I have a code that will find blank cells and copy the above cell to fill in the blank cell and it will stop when it gets to cell value containing the text "total".

But I've been getting this error " Run-time error '91': Object variable or with block variable not set"

Can you please help me fix this code o make I work

Here is the code:

Sub FIll_in_Cells()
 Dim cell As Range
 Dim SearchRange As Range
    On Error Resume Next
    Set SearchRange = Columns("A:A").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

If cell.Value = "Total" Then
GoTo ExitLoop:
End If
 On Error GoTo 0
    If Not SearchRange Is Nothing Then
        For Each cell In SearchRange
            If cell.Row > 1 Then cell = cell.Offset(-1, 0).Value
        Next cell
    End If
   
ExitLoop:
 MsgBox "Done!!!"
 End Sub

Thank you

 

[Fluff]

How about

Sub fawnlemur()
   Dim Fnd As Range
   
   Set Fnd = Range("A:A").Find("Total", , , xlWhole, , , False, , False)
   With Range("A2", Fnd)
      On Error Resume Next
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
      On Error GoTo 0
   End With
End Sub

 

[DanteAmor]

I'll explain, at this moment the cell object does not yet contain a range, that's why you can not use cell.value, that's why the error "Object variable or with block variable not set"

If cell.Value = "Total" Then

You can not ask for cell.value = "Total" because in cell you only have Blanks


The code could be like this

Sub FIll_in_Cells()
    Dim cell As Range
    Dim SearchRange As Range
    Set SearchRange = Columns("A:A").SpecialCells(xlCellTypeBlanks)
    If Not SearchRange Is Nothing Then
        For Each cell In SearchRange
            If cell.Row > 1 Then
                cell = cell.Offset(-1, 0).Value
            End If
        Next cell
    End If
    MsgBox "Done!!!"
End Sub

 

[fawnlemur]

I'll explain, at this moment the cell object does not yet contain a range, that's why you can not use cell.value, that's why the error "Object variable or with block variable not set"

If cell.Value = "Total" Then

You can not ask for cell.value = "Total" because in cell you only have Blanks


The code could be like this

Sub FIll_in_Cells()
Dim cell As Range
Dim SearchRange As Range
Set SearchRange = Columns("A:A").SpecialCells(xlCellTypeBlanks)
If Not SearchRange Is Nothing Then
For Each cell In SearchRange
If cell.Row > 1 Then
cell = cell.Offset(-1, 0).Value
End If
Next cell
End If
MsgBox "Done!!!"
End Sub

Hi

hi thanks for your reply. but wouldn't this just keep looping for ever, as when it gets to the end it will keep going as all cells are blank after that.

the reason I wanted it to stop at "Total" is that is what the bottom cell always contains.







I can't set range as range is always different so it has to be A:A

 

[Fluff]

Have you tried my suggestion in post#2?

 

[DanteAmor]

Hi

hi thanks for your reply. but wouldn't this just keep looping for ever, as when it gets to the end it will keep going as all cells are blank after that.

the reason I wanted it to stop at "Total" is that is what the bottom cell always contains.

I can't set range as range is always different so it has to be A:A

The cycle is not forever, just perform the test. But you can also set the range like this:

Sub Fill_in_Cells_2()
    Set b = Columns("A").Find("Total", lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        On Error Resume Next
        Set SearchRange = Range("A2:A" & b.Row).SpecialCells(xlCellTypeBlanks)
        For Each cell In SearchRange
            If cell.Row > 1 Then
                cell.Value = cell.Offset(-1, 0).Value
            End If
        Next cell
    End If
End Sub

I just wanted to explain the cause of your error and the way to fix it, but you can use the Fluff code.

 

[fawnlemur]

Have you tried my suggestion in post#2?

Hi

I missed it, I have jus tried it works

Thanks

 

[fawnlemur]

The cycle is not forever, just perform the test. But you can also set the range like this:

Sub Fill_in_Cells_2()
    Set b = Columns("A").Find("Total", lookat:=xlWhole, LookIn:=xlValues)
    If Not b Is Nothing Then
        On Error Resume Next
        Set SearchRange = Range("A2:A" & b.Row).SpecialCells(xlCellTypeBlanks)
        For Each cell In SearchRange
            If cell.Row > 1 Then
                cell.Value = cell.Offset(-1, 0).Value
            End If
        Next cell
    End If
End Sub

I just wanted to explain the cause of your error and the way to fix it, but you can use the Fluff code.

I have tried it and it works.

Thank you

 

[DanteAmor]

I'm glad to help you

 

[Fluff]

Hi

I missed it, I have jus tried it works

Thanks

You're welcome & thanks for the feedback