Add a character to the end of cell value?

[TheWennerWoman]

Hello,

What's the correct syntax if I want to add a ] to the end of a cell if it doesn't exist?

I have tried

For a = 2 To lastrow
If Right(Range("E" & a), 1).Value <> "]" Then Range("E" & a).Value = Range("E" & a).Value & "]"
Next a

But this fails with "Runtime error 424: object required".

Many thanks for your thoughts.

 

[Joe4]

I do not see any issues with that code.
What is the value of lastrow?
What do the values in column E look like?
Are they hard-coded, or are there formulas in that column?

 

[TheWennerWoman]

I do not see any issues with that code.
What is the value of lastrow?
What do the values in column E look like?
Are they hard-coded, or are there formulas in that column?

Hardcoded, so

Smith [TR]
Jones [EW]
Harris [SA

I'm trying to trap the third one in that list because it's missing a ] at the end.

lastrow is

With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
End With

 

[Fluff]

You have the .Value in the wrong place, it should be

If Right(Range("E" & a).Value, 1) <> "]" Then

 

[TheWennerWoman]

Genius, many thanks!

 

[Fluff]

You could also get rid of the loop like

   With Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace("if(@="""","""",if(right(@)=""]"",@,@&""]""))", "@", .Address))
   End With

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

You have the .Value in the wrong place, it should be

If Right(Range("E" & a).Value, 1) <> "]" Then

Ah, good catch. My eyes missed that!

 

[Fluff]

I missed it as well, so decided to run it & see what happened.

 

[Joe4]

I missed it as well, so decided to run it & see what happened.

I copied the code, and tried compiling it, and that did not pick up the error. But I did not set up an example to run it on.