Help with this line of macro code

[chuckles1066]

Hope someone can help, here's the code:

If Left(Cells(myrowcounter, 1).Value, 20) = "OUTSTANDING PPM FOR:" Then
            workmannamestart = Right(Cells(myrowcounter, 1).Value, Len(Cells(myrowcounter, 1).Value) - 21)
            workmanname = Left(workmannamestart, 25)

This code then goes on to save various files with the filename workmanname.

And it all works well except there are trailing blank spaces (the original file that the data came from was a text file which may explain it).

I've tried to tidy it by adding the workmanname variable (25 is the longest length of any of the files) but it would be handy to be able to save straightaway without any spaces in the filename.

Something to look at the value of workmannamestart and work out at which position the last character is?

Thanks in advance.

 

[Gary McMaster]

Maybe this ?

workmannamestart = Replace(workmannamestart, " ", "")

Gary

 

[chuckles1066]

That may not work, John Doe would become JohnDoe?

 

[jmthompson]

If Left(Cells(myrowcounter, 1).Value, 20) = "OUTSTANDING PPM FOR:" Then
            workmannamestart = Right(Cells(myrowcounter, 1).Value, Len(Cells(myrowcounter, 1).Value) - 21)
            workmanname = TRIM(workmannamestart)

Does this work?
</pre>

 

[Gary McMaster]

That may not work, John Doe would become JohnDoe?

In that case it will not.

It is based on this case.

without any spaces in the filename

Gary

 

[Gary McMaster]

You could try "Trim". It will remove leading and trailing spaces.

Gary

 

[chuckles1066]

In that case it will not.

It is based on this case.



Gary

Apologies, thanks for your input but I gave you duff info.

It's a "John.Smith .txt", "Peter.Piper .txt" thing, there are varied spaces between the file names and the extension that I'm trying to get rid of.

 

[chuckles1066]

If Left(Cells(myrowcounter, 1).Value, 20) = "OUTSTANDING PPM FOR:" Then
            workmannamestart = Right(Cells(myrowcounter, 1).Value, Len(Cells(myrowcounter, 1).Value) - 21)
            workmanname = TRIM(workmannamestart)

Does this work?
</pre>

I will try it in the morning, many thanks

 

[chuckles1066]

Just as an aside, the filenames are saved in upper case.............can I add a =PROPER command in VB?

 

[Gary McMaster]

I don't think VBA has a native "Proper" function. There is "UCase" & "LCase" for all upper or lower case.

You could use the "WorksheetFunction" like this:

Public Sub Test()
 
Dim sName As String
 
sName = "JOHN SMITH"
sName = Application.WorksheetFunction.Proper(sName)
 
MsgBox sName
 
End Sub

Of course the "WorksheetFunction" would not work in other VBA enabled applications like AutoCAD because it is not a native VBA function.

For your other problem you may have to use various combinations of "Trim", "Replace", "Split" etc to massage your string exactly the way you want it. Maybe replace 2 spaces with 1 space then trim etc.

Gary