Link to home
Create AccountLog in
Avatar of John Carney
John CarneyFlag for United States of America

asked on

Formatting last 5 characters of a cell

This macro bugs on the last line: 
If InStr(UCase(cel), "MAINT") > 0 Or InStr(UCase(cel), "DKFL") > 0 Then
cel.Characters(Start:=x, Length:=y).Font.FontStyle = "Bold"
cel.Characters(Start:=x, Length:=y).Font.Size = 14
cel.Characters(Start:=x, Length:=y) = UCase(cel.Characters(Start:=x, Length:=y))
End If

Open in new window

What baffles me is that this line works: 
cel.Characters(Start:=x, Length:=y).delete

Open in new window

but this line doesn't: 
cel.Characters(Start:=x, Length:=y) =ucase(cel.Characters(Start:=x, Length:=y))

Open in new window

How should I write that line?

Thanks,
John
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
When you say the "macro bugs" what error do you get?
Avatar of John Carney
John Carney
Flag of United States of America image

ASKER

"Object doesn't support this property or method."
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of John Carney
John Carney
Flag of United States of America image

ASKER

Perfect, thanks!
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
Does this work?

cel.Characters(Start:=x, Length:=y).Caption = UCase(cel.Characters(Start:=x, Length:=y).Caption)
I just tested your solution and it does work. I didn't even know a cell had a Caption property:(
In any case I don't expect any points for this but this also works and to me it's more "normal".

cel.Characters(Start:=x, Length:=y).Text = UCase(cel.Characters(Start:=x, Length:=y).Text)