Solved: Error msg" Object variable or with block variable not set"

[benjaminyaon]

Hi there, i have the following codes, when i run it, pop up an error mag windows indicated "Object variable or with block variable not set"..

what does it mean? how can i change it? please help, thanks,


Sub a()
Dim arr As Range, target As Range
Dim Resultarr As Range
Set arr = ActiveSheet.UsedRange
For Each target In arr
If target.Interior.ColorIndex = 23 Then
If Resultarr Is Nothing Then
Set Resultarr = target
Else
Set Resultarr = Application.Union(Resultarr, target)
End If
End If
Next target
Resultarr.ClearContents
End Sub

[Jan Karel Pieterse]

It probably means the code did not find any cells with colorindex 23.
Make sure the last line of the sub is changed from:
Resultarr.ClearContents
to:
If Not Resultarr Is Nothing Then
Resultarr.ClearContents
End If

[Paul_Hossler]

Courious as to why you build a Union and then clear it, instead of doing the cell as you fine it


Sub a()
Dim arr As Range, target As Range
Dim Resultarr As Range
Set arr = ActiveSheet.UsedRange
For Each target In arr.Cells
If target.Interior.ColorIndex = 23 Then target.ClearContents
Next target
End Sub


Paul

[benjaminyaon]

It probably means the code did not find any cells with colorindex 23.
Make sure the last line of the sub is changed from:
Resultarr.ClearContents
to:
If Not Resultarr Is Nothing Then
Resultarr.ClearContents
End If

this one helps, thanks so much...

[benjaminyaon]

Courious as to why you build a Union and then clear it, instead of doing the cell as you fine it


Sub a()
Dim arr As Range, target As Range
Dim Resultarr As Range
Set arr = ActiveSheet.UsedRange
For Each target In arr.Cells
If target.Interior.ColorIndex = 23 Then target.ClearContents
Next target
End Sub


Paul


this one also works, thanks so much...

[benjaminyaon]

It probably means the code did not find any cells with colorindex 23.
Make sure the last line of the sub is changed from:
Resultarr.ClearContents
to:
If Not Resultarr Is Nothing Then
Resultarr.ClearContents
End If


Courious as to why you build a Union and then clear it, instead of doing the cell as you fine it


Sub a()
Dim arr As Range, target As Range
Dim Resultarr As Range
Set arr = ActiveSheet.UsedRange
For Each target In arr.Cells
If target.Interior.ColorIndex = 23 Then target.ClearContents
Next target
End Sub

Paul

One more last question, how can i get this code work for the whole workbook(all worksheet), I can only get it works for the active sheet, how can i apply this code? please advise. Thanks

[Bob Phillips]

Sub a()
Dim arr As Range, target As Range
Dim Resultarr As Range
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets

Set arr = sh.UsedRange
For Each target In arr.Cells
If target.Interior.ColorIndex = 23 Then target.ClearContents
Next target
Next sh
End Sub

[Bob Phillips]

and for the Union method



Sub a()
Dim arr As Range, target As Range
Dim Resultarr As Range
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
Set arr = sh.UsedRange
For Each target In arr
If target.Interior.ColorIndex = 23 Then
If Resultarr Is Nothing Then
Set Resultarr = target
Else
Set Resultarr = Application.Union(Resultarr, target)
End If
End If
Next target
If Not Resultarr Is Nothing Then Resultarr.ClearContents
Set Resultarr = Nothing
Next sh
End Sub

[benjaminyaon]

and for the Union method



Sub a()
Dim arr As Range, target As Range
Dim Resultarr As Range
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
Set arr = sh.UsedRange
For Each target In arr
If target.Interior.ColorIndex = 23 Then
If Resultarr Is Nothing Then
Set Resultarr = target
Else
Set Resultarr = Application.Union(Resultarr, target)
End If
End If
Next target
If Not Resultarr Is Nothing Then Resultarr.ClearContents
Set Resultarr = Nothing
Next sh
End Sub



IT WORKS PERFECT, THANK YOU SO MUCH

I AM VERY NEW TO VBA...:clap: :clap: :clap: