Object Variable or With block not set error

A

Ayo

I am getting the above error when I run a code containing the segment of
codes below. The error occurs at the Selection.Replace...... line. What I am
trying to do is perform a find and replace action.
With objSht
.Range(.Cells(4, 1), .Cells(conMAX_ROWS, intLastCol)).ClearContents
.Range("A4").CopyFromRecordset rs
.Range(.Cells(4, 1), .Cells(conMAX_ROWS, intLastCol)).Select
Selection.Replace What:="False", Replacement:="No",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="True", Replacement:="Yes",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End With
 
D

Douglas J. Steele

Access knows nothing about Selection: you need to relate it back to your
Excel objects.

Try:

With objSht
.Range(.Cells(4, 1), .Cells(conMAX_ROWS, intLastCol)).ClearContents
.Range("A4").CopyFromRecordset rs
With .Range(.Cells(4, 1), .Cells(conMAX_ROWS, intLastCol))
.Replace What:="False", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="True", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace multiple values in one statement 3
Find and Replace is going wrong in some cases 10
Macro Not Changing Date Values 7
Tidy up multiple find and replace code 2
Macro to select columns and replace data 0
Find and Replace Help 3
Help with a loop please. 10
Need help with a "If" "Else" VB code 4

Top