shorten value in a cell

[Guest]

Hello,

I just found an unexpected problem.
I have a sheet with a list of suppliers
I create new sheets in my macro that are called after suppliers. One sheet
one supplier.
And the problem occurs when I'm creating new sheets.
Some supplier's names are longer than 31 char. and sheet's name cannot be
longer than 31 characters.If so run-time errror occurs
I'd need to validate supplier's name before creating the new sheet.
If that name is longer than 31 char I would like to shorten supplier's name
so that is shorter than 31 char and I don't know how.

can anybody help me please

Thanks
JH

 

[R.VENKATARAMAN]

I experimented with the sheetname as rvenkataraman
and then used this code

Public Sub test()
Dim x As String
x = ActiveSheet.Name
MsgBox x

If Len(x) > 10 Then x = Left(x, 5)
ActiveSheet.Name = x
MsgBox ActiveSheet.Name
End Sub


you can introduce a loop to check all the worksheets in the workbook and use
this sub in each loop

 

[Norman Jones]

Hi JH,

Try something like:

Sub Tester01()
Dim cell As Range
For Each cell In Range("MyList").Cells
With cell
Sheets.Add.Name = IIf(Len(.Value) > 31, _
Left(.Value, 31),
..Value)
End With
Next cell

End Sub

 

[Guest]

You can run your test like this:

Sub test()

If Len(Range("A1")) > 31 Then
supplierName = Mid(Range("A1"), 1, 31)
Else
supplierName = Range("A1")
End If

Then, for you sheet name, simply use the variable "supplierName".

Regards,
Robert
End Sub

 

[Guest]

thank you

JH

 

[Tom Ogilvy]

Dim rng as Range, cell as Range
set rng = selection
for each cell in selection
worksheets.Add After:=Worksheets(worksheets.count)
ActiveSheet.Name = Left(Cell.value,31)
Next

There is no reason to add an additional check if the length is greater than
31. If it is less than or equal 31, you get the original value and if more
than 31, you get the left 31 characters. Demo from the immediate window:

? len(Left("ABCD",31))
4