Syntax error

[erimhast83]

Can anyone spot the error in my code causing the syntax error? I am trying to use a loop to replace all blank cells in column D with the word homeless until column B has a blank cell and then the loop will end.


Do Until IsEmpty(ActiveCell.Offset(-2, 0))

ActiveCell.Replace What:="", Replacement:="Homeless", LookAt:=xlPart,
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.FindNext(After:=ActiveCell).Activate
Loop

 

[joeu2004]

Do you want to give us a hint about which line(s) that VBA complains about?

Or is that part of the test? <wink>

Well, I do see an obvious syntax error: missing underbar after xlPart in the ActiveCell.Replace line.

I don't know if you copy-and-pasted the text from VBA, or if you retyped it. Certainly, copy-and-paste is the right way to do it.</wink>

 

[Fluff]

Along with what joeu2004 has said
Your first line of code does not match your description. It's currently looking 2 rows above the active cell, not 2 columns to the left.

 

[erimhast83]

ActiveCell.Replace What:="", Replacement:="Homeless", LookAt:=xlPart,
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I did copy and paste from VBA and adding the _ after xlpart did not help .

Do you want to give us a hint about which line(s) that VBA complains about?

Or is that part of the test? <wink>

Well, I do see an obvious syntax error: missing underbar after xlPart in the ActiveCell.Replace line.

I don't know if you copy-and-pasted the text from VBA, or if you retyped it. Certainly, copy-and-paste is the right way to do it.</wink>

 

[joeu2004]

I did copy and paste from VBA and adding the _ after xlpart did not help

I suspect you applied my suggestion literally, and put the underscore in the wrong place.

My bad! I should have provided a concrete example.

Do Until IsEmpty(ActiveCell.Offset(-2, 0))
   ActiveCell.Replace What:="", Replacement:="Homeless", LookAt:=xlPart, _
       SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
   Cells.FindNext(After:=ActiveCell).Activate
Loop

Without the underscore after xlPart as shown, VBA highlights the 3 lines from ActiveCell.Replace through ReplaceFormat in red, indicating a syntax error.

With the underscore after xlPart as shown, VBA accepts the syntax.

Note that I am only answering your syntax question.

I did not bother to analyze the code to see if it does what you might expect.