Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

MottoK

Technical User
Oct 18, 2006
73
GB
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
 
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?!
 
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.
 
Sorry. This is why I should test my code, which I can't do when I post from my iPad...
Code:
        Do While r <= .Cells(.Cells.Rows.Count, "L").End(-4162)[highlight].Row[/highlight]

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.
 
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Not at all Skip, wouldn't have been able to figure it out myself so many many thanks.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top