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

 

[SkipVought]

hi,

    ColumnCount = objWorkbook.Sheets(1).UsedRange.Columns.Count
    
    Dim r As Object
    
    With objWorkbook.Sheets(1)
        For Each r In Intersect(.Cells(1, "L").EntireColumn, .UsedRange)
            With r
                .Value = Left(.Value, Len(.Value) - 1) & UCase(Right(.Value, 1))
            End With
        Next
    End With
    
    If ColumnCount <> 12 Then

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

 

[MottoK]

Thank you so much for this Skip. Unfortunately I'm having problems getting this to run. I'm running this as a .vbs file and am getting errors such as "Expected end of statement" due to the Dim r as Object line (when I take out 'as Object') I get "Type mismatch 'Intersect'". Any ideas how I can run this? Thank you.

 

[Andrzejek]

You are in "VBA Visual Basic for Applications (Microsoft) Forum", so Skip gave you VBA solution.
The easiest way to use it would be to (In Excel):
Hit 'record a macro'
record anything
stop record
go to VBA IDE (Alt-F11)
find your recorded macro (in the Modules - Module1, usually)
replace whatever you recorded with Skips code between

Sub Macro1()[green]
'
' Macro1 Macro
' Skips code goes here[/green]

End Sub

Go back to Excel and run Macro1

Have fun.

---- Andy

 

[MottoK]

Hi Andy, appreciate your reply but unfortunately there is no way I can do this within Excel, there are just too many other factors/code/scheduled bat files etc in my project to use Excel directly so I need to be able to script this in a .vbs file (as in first post).

Its because I don't have any experience with this that I didn't realize code from VBA wouldn't work on a vbs script. Should I take this to the vbscript forum or is there anyone who is able to show me how to change the code Skip gave me to work in a .vbs file please?

Many thanks, really appreciated.

 

[SkipVought]

    Dim r As long
    
    With objWorkbook.Sheets(1)
        For r = 1 to Intersect(.Cells(1, "L").EntireColumn, .UsedRange).rows.count
            With .cells(r, "L").value
                .Value = Left(.Value, Len(.Value) - 1) & UCase(Right(.Value, 1))
            End With
        Next
    End With

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

 

[MottoK]

Thanks but still not working, same errors as before. I'm literally double clicking a .vbs file to run this - didn't think vbscript supports data types? So close and yet so far!!

 

[SkipVought]

        For r = 1 to objExcel.Intersect(.Cells(1, "L").EntireColumn, .UsedRange).rows.count
[/code

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

 

[MottoK]

Getting closer anyway. I can't set r as anything because vbscript doesn't allow data types so the code so far is as below. I now get an error saying "Object required '[string: "manager"]'. "manager" is the header string of column L. monitor is close to going out of window! Thanks for your patience!


Dim r
With objWorkbook.Sheets(1)
For r = 1 to objExcel.Intersect(.Cells(1, "L").EntireColumn, .UsedRange).rows.count
With .cells(r, "L").value
.Value = Left(.Value, Len(.Value) - 1) & UCase(Right(.Value, 1))
End With
Next
End With

 

[SkipVought]

For r = [highlight]2[/highlight] To objExcel.Intersect(.Cells(1, "L").EntireColumn, .UsedRange).Rows.Count

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

 

[MottoK]

haha - I love how cool these reply's are. Unfortunately not that easy, the same error occurs for every row in the entire 'L' column. :-(

 

[SkipVought]

    With objWorkbook.Sheets(1)
        r = 2
        
        Do
            With .Cells(r, "L")
                If .Value = "" Then Exit Do
                .Value = Left(.Value, Len(.Value) - 1) & UCase(Right(.Value, 1))
            End With
            
            r = r + 1
        Loop
    End With

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

 

[MottoK]

Well it works! Only problem is it stops whenever there is a blank cell, I'll try and figure it out. Thank you so much for your help Skip, brilliant.

 

[Andrzejek]

With the blank cell it hits this line of code:

If .Value = "" Then Exit Do

That's why it quits.

Have fun.

---- Andy

 

[MottoK]

Yeh I see the line of code but unfortunately there are one or two blanks in the spreadsheet so only the first half get changed.

 

[SkipVought]

How do I code thee / Let me count the ways"

    Dim r
    With objWorkbook.Sheets(1)
        r = 2
        
        Do While r <= .Cells(.Cells.Rows.Count, "L").End(-4162)
            With .Cells(r, "L")
                .Value = Left(.Value, Len(.Value) - 1) & UCase(Right(.Value, 1))
            End With
            
            r = r + 1
        Loop
    End With

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

 

[MottoK]

Starting to feel really bad now Skip, "invalid procedure call or argument 'Left'" - guess it must be because it's trying to apply Left to a blank cell and I guess the same will happen when it gets to the Ucase as well. Sorry!

 

[SkipVought]

            With .Cells(r, "L")
                If .Value <> "" Then
                    .Value = Left(.Value, Len(.Value) - 1) & UCase(Right(.Value, 1))
                End If
            End With

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

 

[MottoK]

remote procedure call failed" but I'll double check I applied the code correctly when I get home. thank you.

 

[MottoK]

Code so far is below but when I run it nothing seems to happen, it is however opening Excel, when I close via task mgr I get the "remote procedure call failed" error?


Dim r
With objWorkbook.Sheets(1)
r = 2

Do While r <= .Cells(.Cells.Rows.Count, "L").End(-4162)
With .Cells(r, "L")
If .Value <> "" Then
.Value = Left(.Value, Len(.Value) - 1) & UCase(Right(.Value, 1))
End If
End With

r = r + 1
Loop
End With