VBA code to change last letter of string to uppercase on one column 1

[MottoK]

Hi all, would someone please be able to tell me what code to add to the code below to enable me to change the last letter of every string in column 'L' on my spreadsheet please? This is an adhoc thing for work so don't know much about VBA. This is a .vbs file being called from a .bat file.

Very very much appreciated, thanks.



DIM objFSO
DIM src_file
DIM objExcel
DIM objWorkbook
DIM ColumnCount

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName("AD_iTrent_UserDetails.xls")

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Trent_AD\AD_iTrent_UserDetails.XLS")


ColumnCount = objWorkbook.Sheets(1).UsedRange.Columns.count

If ColumnCount <> 12 then


objWorkbook.Close False
objExcel.Quit
wscript.quit

Else

objWorkbook.Close False
objExcel.Quit

End if

 

[MottoK]

Now error says "The remote server machine does not exist or is unavailable: '(Cells...).End'" - what the heck is that all about it isn't even calling a remote machine?!

 

[MottoK]

Don't leave me! Appreciate the help so far with this everyone but still not working, seems to be around the If .Value <> "" Then bit of code so if anyone has any ideas that would be very much appreciated. Thanks.

 

[SkipVought]

Sorry. This is why I should test my code, which I can't do when I post from my iPad...

        Do While r <= .Cells(.Cells.Rows.Count, "L").End(-4162)[highlight].Row[/highlight]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MottoK]

Woohoo that's it! Thank you so much for that SKip, didn't realise it would be such a headache. can't believe I saw that .Row while searching for an answer but was convinced it was around the If statement, id*ot that I am. Pity we can't test from ipads right enough, as I'm waiting to hear if Apple are going to announce the iPad 5 today, here's hoping. Many thanks again.

 

[SkipVought]

so sorry that it took me so long to dial in on the solution. Too much windage, coriolis, the Syrian question, the dog ate it... y'know ANYTHING for an excuse! ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MottoK]

Not at all Skip, wouldn't have been able to figure it out myself so many many thanks.