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

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

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

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

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

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

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

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