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

Please Login or Register  to view this content.

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

[rylo]

Kelvin

Try building a small sample file that will definitely show the error and put it up for review.

And, as Steel Monkey advises, go back and wrap the code in your original post.

rylo

[KMCK]

Thanks Guys

Sample data attched - active cell needs to be SYS!$F$2 to run the macro.

Macro below again, hopefully wrapped correctly this time....

Any help much appreciated.

Kelvin

Please Login or Register  to view this content.

[rylo]

Kelvin

Where should the cursor be on sheet2 when this processes? Perhaps you could update the code to make sure you nominate a cell on the relevant sheet before each activecell action. I'm crashing out at the moment, but I think it has to do with the position on the sheet.

Also, your sample file didn't have a sheet2 ready for action.


rylo

[KMCK]

Sorry - copied the sample data set incorrectly, revised version attached.

The cursor shouldn't need to be on Sheet 2 when this processes - as long as the initial active cell is SYS!$f$2 it should run.

I'm not sure why the error is ocurring - earlier in teh mcro teh following runs OK:

Please Login or Register  to view this content.

This seems to me to be the same as thr following, which does error, apart from the find value being different:

Please Login or Register  to view this content.

Thanks
Kelvin

[rylo]

Hi

I picked up your new sample file, created a general module, inserted the code and ran it.

Code bombed out on sheet Count with column F highlighted. There was nothing in this column, so the selection.find....activate section errored.

If this is not the right place, can you put your code into the sample file as I have just done, run it and see if you get the same error. Original reason (there was no result of the find to match) holds at this time.


rylo

[KMCK]

Found the problem!.....when I moved to the count sheet I did not specify the active cell, so the vlookup was being applied from the incorrect starting point.
Now fixed with an active cell starting point.

Thanks for your help rylo.

Cheers
Kelvin

[rylo]

Kelvin

It is usually wise to specify cells that are being processed - either by directly selecting a cell (as in this case) or using variables to specify which cell is being actioned.

Glad you got it resolved.

rylo