Problem limiting characters

[Patrick Simonds]

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

 

[Don Guillett]

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

 

[Patrick Simonds]

Returns no value to column A

try
Me.Range("A2:A301").Value = Left(Me.Range("B2:B301"),15).value

 

[Tom Ogilvy]

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

 

[Patrick Simonds]

Is there anyway to convert the current/active cell (after the value is
assigned) to display only first 15 characters?

 

[Tom Ogilvy]

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.

 

[Patrick Simonds]

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]

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