Error - object variable or with block variable not set

[KMCK]

Hi

I've got a simple Macro which manipulates data in various worksheets. Problem is that I get the error: "object variable or with block variable not set"
in the area of the code that I have put in bold below:

All I'm trying to do in this part is find the first occurrence of the #n/a value

Can anyone please suggest what the problem is, and what the resolution might be?

Thanks!


ActiveCell.FormulaR1C1 = "=ISERROR(VLOOKUP(RC[-5],Count!C[-5]:C[-2],4,FALSE))"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & Range("E2").End(xlDown).Row)
Range("F:F").Select
Columns("F:F").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("F2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("F:F").Select
Selection.Find(What:="false", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Rows(ActiveCell.Row).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Complete matches"
Range("A1").Select
ActiveSheet.Paste
Sheets("SYS").Select
ActiveWindow.ScrollRow = 1
Columns("F:F").Select
Selection.ClearContents
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Count!C[-4]:C[-1],4,FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & Range("E2").End(xlDown).Row)
Range("F:F").Select
Columns("F:F").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Replace What:="#n/a", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns("C:F").Select
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Variances"
Range("A1").Select
ActiveSheet.Paste
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Counted"
Sheets("Count").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],SYS!C[-4]:C[-1],4,FALSE)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & Range("E2").End(xlDown).Row)
Range("F:F").Select
Columns("F:F").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("F:F").Select
Selection.Find(What:="#n/a", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Rows(ActiveCell.Row).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveWindow.ScrollRow = 1
Range("H1").Select
ActiveSheet.Paste
Range("J1:M1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Variances").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=15
Range("A301").Select
ActiveSheet.Paste
Columns("D:D").Select
Range("D272").Activate
Application.CutCopyMode = False
Selection.Replace What:="#n/a", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
ActiveWindow.ScrollRow = 1
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E" & Range("D2").End(xlDown).Row)
Range("E:E").Select
'Selection.AutoFill Destination:=Range("E2:E600")
'Range("E2:E600").Select
Columns("E:E").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E1").Select
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.FormulaR1C1 = "Difference"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("F1").Select
End Sub

[rylo]

Hi

Are there any #N/A errors in the data you are trying to process at that stage in the macro?


rylo

[KMCK]

Hi

No - there were previously from a vlookup, but then I had done a copy / paste values into this column.
Do you think it is the #n/a value that is causing teh problem - I can always replace it with something else?

Thanks

[rylo]

Hi

That's what I think is happening. I dummied something up and when there were no items found for the first find (on the false), I got the same problem.

One way to handle this is to do a set

set findit = range("F:F").find what ......
if not findit is nothing then.....

Basically you see if there is going to be anything found before you try to do the find.select scenario.

Another way is to do countif or something similar. Then you process if the count is > 0


rylo

[KMCK]

Hi

But there are definitely some "#n/a" values to be found, there always will be with this data set that I am manipulating.
It just seems as though there is some sort of issue with them being found?

Earlier in the macro I'm trying to find "false" values with:

Selection.Find(What:="false", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

and this works OK, so I'm a bit puzzled why the equivalent doesn't work further on?!
Is it forgetting what the active worksheet is?

Thanks
Kelvin

[Steel Monkey]

Hi Kelvin

Just for future reference you might want to wrap code you submit in a post with code tags. You dont want to risk the moderators locking or deleting your thread for not complying with the rules

If your not sure how to do that see the below link:

http://www.excelforum.com/misc.php?do=bbcode#code