Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
shorten value in a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
shorten value in a cell
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 JH wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
shorten value in a cell
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 --- Regards, Norman "JH" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
shorten value in a cell
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
shorten value in a cell
thank you
JH "cmart02" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
shorten value in a cell
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 -- Regards, Tom Ogilvy "JH" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there away to shorten | Excel Worksheet Functions | |||
Can I shorten this any? | Excel Worksheet Functions | |||
Any way to shorten this up? | Excel Worksheet Functions | |||
Shorten A Name | Excel Discussion (Misc queries) | |||
Can I shorten this? | Excel Programming |