DT POR Macro

[kool_jaysmiles]

I have created a macro which is to find an excat match in the column. It works for other seasons except fall. Can you please help. This is the error message that I get."Object Variable or with block variable not set”. The debugger shows the error here:

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

I tried changing (LookAt_:=xlPart ) the part to whole, but does not work. I can send over the sheets if someone can help.

 

[prajul89]

I hope the Selection part contains "B".
please double check.

 

[kool_jaysmiles]

Yes it does....It does not copy the values for some particular columns....

 

[prajul89]

Please run the macro in Break mode and see which column it selects when the Macro Finds for the value "B".

 

[taurean]

This is the error message that I get."Object Variable or with block variable not set”. The debugger shows the error here:

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

I tried changing (LookAt_:=xlPart ) the part to whole, but does not work. I can send over the sheets if someone can help.

This error means you need an object variable against this statement which VBA expects it to be there but does not find it. In your case, the object is range. So you have approach this as like:

[FONT=Calibri][COLOR=black]Dim r as Range[/COLOR][/FONT]
[FONT=Calibri]Set r = [/FONT][COLOR=#1f497d][FONT=Calibri][COLOR=red]Selection[/COLOR].Find(What:="B", After:=ActiveCell, LookIn:=xlFormulas, LookAt _[/FONT][/COLOR]
[FONT=Calibri][COLOR=#1f497d]:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=_[/COLOR][/FONT]
[FONT=Calibri][COLOR=#1f497d]False, SearchFormat:=False)[/COLOR][/FONT]

Don't use Select, Selection and Activate while using macros. They come in due to macro recorder.

 

[prajul89]

If you are using the above mention code then this code will also help when you don't find B

If r Is Nothing Then Goto Somewhere:

Where Somwhere: is the place where the macro should proceed if B is not found. or just put 'Exit Sub' if you want exit macro if B is not found.