Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
Not sure why the code below does not work. It woks fine if I use
Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
try
Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Me.Range("A2:A301").Value = Left(Me.Range("B2:B301"),15).value -- Don Guillett SalesAid Software "Patrick Simonds" wrote in message ... Not sure why the code below does not work. It woks fine if I use Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
Returns no value to column A
"Don Guillett" wrote in message ... try Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Me.Range("A2:A301").Value = Left(Me.Range("B2:B301"),15).value -- Don Guillett SalesAid Software "Patrick Simonds" wrote in message ... Not sure why the code below does not work. It woks fine if I use Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
You would need to do it a cell at a time - left can't operate on a
multicell range. Private Sub Worksheet_Calculate() Dim cell as Range On Error GoTo Finish Module1.UnprotectWorkSheet for each cell in Me.Range("A2:A301") cell.Value = Left(cell.offset(0,1).Value, 15) Next Finish: End Sub -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Not sure why the code below does not work. It woks fine if I use Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
Is there anyway to convert the current/active cell (after the value is
assigned) to display only first 15 characters? "Tom Ogilvy" wrote in message ... You would need to do it a cell at a time - left can't operate on a multicell range. Private Sub Worksheet_Calculate() Dim cell as Range On Error GoTo Finish Module1.UnprotectWorkSheet for each cell in Me.Range("A2:A301") cell.Value = Left(cell.offset(0,1).Value, 15) Next Finish: End Sub -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Not sure why the code below does not work. It woks fine if I use Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
I don't think you can with formatting, but you could possibly use this
approach if you don't want to loop: Private Sub Worksheet_Calculate() On Error GoTo Finish 'Module1.UnprotectWorkSheet Application.EnableEvents = False With Me.Range("A2:A301") .Formula = "=Left(B2,15)" ' optional .Formula = .Value End With Finish: Application.EnableEvents = True End Sub This should work if you have constants in column B. -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Is there anyway to convert the current/active cell (after the value is assigned) to display only first 15 characters? "Tom Ogilvy" wrote in message ... You would need to do it a cell at a time - left can't operate on a multicell range. Private Sub Worksheet_Calculate() Dim cell as Range On Error GoTo Finish Module1.UnprotectWorkSheet for each cell in Me.Range("A2:A301") cell.Value = Left(cell.offset(0,1).Value, 15) Next Finish: End Sub -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Not sure why the code below does not work. It woks fine if I use Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
Thank you sir, that worked. It still loops through all 300 rows but if need
be I can live with that. I did have to drop the optional .Formula = .Value. "Tom Ogilvy" wrote in message ... I don't think you can with formatting, but you could possibly use this approach if you don't want to loop: Private Sub Worksheet_Calculate() On Error GoTo Finish 'Module1.UnprotectWorkSheet Application.EnableEvents = False With Me.Range("A2:A301") .Formula = "=Left(B2,15)" ' optional .Formula = .Value End With Finish: Application.EnableEvents = True End Sub This should work if you have constants in column B. -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Is there anyway to convert the current/active cell (after the value is assigned) to display only first 15 characters? "Tom Ogilvy" wrote in message ... You would need to do it a cell at a time - left can't operate on a multicell range. Private Sub Worksheet_Calculate() Dim cell as Range On Error GoTo Finish Module1.UnprotectWorkSheet for each cell in Me.Range("A2:A301") cell.Value = Left(cell.offset(0,1).Value, 15) Next Finish: End Sub -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Not sure why the code below does not work. It woks fine if I use Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem limiting characters
There is no looping code - if you want to say internally, excel does that,
then you can say that about any solution. At some level there is a loop. If the real issue is that all the cells are not filled, then perhaps Private Sub Worksheet_Calculate() Dim lastrow as Long On Error GoTo Finish 'Module1.UnprotectWorkSheet Application.EnableEvents = False lastrow = me.cells(rows.count,2).End(xlup) With Me.Range("A2").Resize(lastrow-1,1) .Formula = "=Left(B2,15)" ' optional ' .Formula = .Value End With Finish: Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Thank you sir, that worked. It still loops through all 300 rows but if need be I can live with that. I did have to drop the optional .Formula = .Value. "Tom Ogilvy" wrote in message ... I don't think you can with formatting, but you could possibly use this approach if you don't want to loop: Private Sub Worksheet_Calculate() On Error GoTo Finish 'Module1.UnprotectWorkSheet Application.EnableEvents = False With Me.Range("A2:A301") .Formula = "=Left(B2,15)" ' optional .Formula = .Value End With Finish: Application.EnableEvents = True End Sub This should work if you have constants in column B. -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Is there anyway to convert the current/active cell (after the value is assigned) to display only first 15 characters? "Tom Ogilvy" wrote in message ... You would need to do it a cell at a time - left can't operate on a multicell range. Private Sub Worksheet_Calculate() Dim cell as Range On Error GoTo Finish Module1.UnprotectWorkSheet for each cell in Me.Range("A2:A301") cell.Value = Left(cell.offset(0,1).Value, 15) Next Finish: End Sub -- Regards, Tom Ogilvy "Patrick Simonds" wrote in message ... Not sure why the code below does not work. It woks fine if I use Me.Range("A2:A301").Value = Me.Range("B2:B301").Value But I need the returned value to be limited to only the first 15 characters. When I run the code below, no value is placed into column A. Private Sub Worksheet_Calculate() On Error GoTo Finish Module1.UnprotectWorkSheet Me.Range("A2:A301").Value = Left(Me.Range("B2:B301").Value, 15) Finish: End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Characters limiting | Excel Worksheet Functions | |||
Problem with Data Validation and limiting characters | Excel Worksheet Functions | |||
limiting characters in a cell | Excel Programming | |||
Limiting Characters In Rows | Excel Discussion (Misc queries) | |||
Limiting characters in a cell | Excel Worksheet Functions |