Help please: There is the following macro:

Private Sub Worksheet_Change(ByVal Target As Range) Dim objCell As Range With Target If .Column < 1 Or .Column > 5 Or .Row < 1 Or .Row > 100000000 Then Exit Sub End With For Each objCell In Target If TypeName(objCell.Value) = "String" Then objCell.Value = Left(objCell.Value, 10) End If Next End Sub

It responds to a change in the cells of the range <1 or> 5. And if it is greater than 10, it cuts the value to 10. How can I add this check in several columns? That is, you need something like:

 Private Sub Worksheet_Change(ByVal Target As Range) Dim objCell As Range With Target If .Column < 1 Or .Column > 5 Or .Row < 1 Or .Row > 100000000 Then Exit Sub End With For Each objCell In Target If TypeName(objCell.Value) = "String" Then objCell.Value = Left(objCell.Value, 10) End If With Target If .Column < 7 Or .Column > 9 Or .Row < 1 Or .Row > 100000000 Then Exit Sub End With For Each objCell In Target If TypeName(objCell.Value) = "String" Then objCell.Value = Left(objCell.Value, 4) End If Next End Sub

But at the same time swears. Help me please.

  • forum.msexcel.ru/index.php/topic,11512.0.html Instead of showing a normal example, did you start running around the network? - vikttur
  • And what is my example - is not normal? - Kamil Askerov
  • I didn’t really want anything - just to show how to combine the 2 limitations. - Kamil Askerov
  • Counter question: what is my code abnormal? It combines two limitations. If it does not work, there was a request to show it in the file. - vikttur
  • Perhaps something else is meant, not an event of a change in the range of variable cells? The question of combining the two conditions is shown. - vikttur

2 answers 2

The code assumes that changes were made in the range of cells in a single column.

Where did you see a column or row with a number less than 1? :) Why specify a limit on a line below a millionth?

 Private Sub Worksheet_Change(ByVal Target As Range) Dim objCell As Range With Target If .Column > 9 Then Exit Sub For Each objCell In Target If TypeName(objCell.Value) = "String" Then If .Column < 6 Then objCell.Value = Left(objCell.Value, 10) Else objCell.Value = Left(objCell.Value, 4) End If End If Next End With End Sub

To eliminate errors when processing values, add a check for the length of the value.

You need to disable-enable EnableEvents events. The performance in this case does not affect (but the cells are reviewed several times, which is also not good), but in other cases it is possible to enter an infinite loop.

If you use an additional variable, you can get away from the comparison in the loop and thereby speed up the work:

 Private Sub Worksheet_Change(ByVal Target As Range) Dim objCell As Range Dim lClmn As Long With Target If .Column > 9 Or .Row > 100000000 Then Exit Sub If .Column < 6 Then lClmn = 10 Else: lClmn = 4 End With Application.EnableEvents = False For Each objCell In Target If Len(objCell.Value) > lClmn Then If TypeName(objCell.Value) = "String" Then objCell.Value = Left(objCell.Value, lClmn) End If End If Next Application.EnableEvents = True End Sub

Instead of If .Column> 9 Then Exit Sub, you can impose a column constraint like this:

 If Not Application.Intersect(Range("A:I"), Target) Is Nothing Then ... End If
  • If Not Application. Intersect (Range ("A: H"), Target) "String" Then If .Column = 2 Then bjCell.Value = Left (objCell.Value, 10) Else bjCell.Value = Left (objCell.Value, 4) This does not work - Kamil Askerov
  • Again your code did not work. - Kamil Askerov
  • I inserted the code into the sheet module - it works for me. Once again: show in your topic FILE EXAMPLE with an error. Or to do everything instead of you (including making a mistake)? - vikttur
  • For me to do nothing. I asked for an example of two functions in the example and that's it. The error is that nothing works as it should - does not make the necessary restrictions - does not cut the string to a certain amount. - Kamil Askerov
  • I added test.xls and added your code in the macro. - Kamil Askerov

First, your code should swear at the lack of Next to complete the first For block. Second, use the If ... Then ... ElseIf ...Then ... End If construct. In your case, you should get something like

 Private Sub Worksheet_Change(ByVal Target As Range) Dim objCell As Range With Target If .Column < 5 And .Row < 100000000 Then For Each objCell In Target If TypeName(objCell.Value) = "String" Then objCell.Value = Left(objCell.Value, 10) End If Next objCell ElseIf .Column > 7 And .Column < 9 And .Row < 100000000 Then For Each objCell In Target If TypeName(objCell.Value) = "String" Then objCell.Value = Left(objCell.Value, 4) End If Next End If End With End Sub