0

How to make macro work with all cells of column A? So that can paste into several cells at a time and the data is converted. I need this in order to be able to convert multiple data at once.

I have this macro:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim v As Variant
    v = Split(Target, " ")
    If UBound(v) <> 1 Then Exit Sub
    If Right(Target, 1) <> "m" Then
        Target = v(1) & " " & v(0)
    End If
End Sub

This macro converts the data to (any number)m and (any number)M be on the right.

For example:

L3 280M
500m FMA
Nest 475m
340m Pr6
720M uT10
etc.

Convert to:

L3 280M
FMA 500m
Nest 475m
Pr6 340m
uT10 720M
etc.
Improve this question
1
  • add a loop to your macro
    – gns100
    Commented Sep 12, 2022 at 20:24

1 Answer 1

Reset to default
1

May be so?

Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
Dim v As Variant
    If Application.WorksheetFunction.CountA(Target) < 1 Then Exit Sub
    For Each oCell In Target.Cells
        If oCell.Column = 1 Then
            v = Split(oCell.Text, " ")
            If UBound(v) = 1 Then
                If Right(v(1), 1) <> "m" Then
                    oCell.Value = v(1) & " " & v(0)
                End If
            End If
        End If
    Next oCell
End Sub
Improve this answer
3
  • Thank you! Works great! But if try to delete all the data in column A — click on column A (so that it is all selected) and press the "Delete" key on the keyboard, then Excel will temporarily freeze.
    – moninah
    Commented Sep 13, 2022 at 11:16
  • 1
    Of course it freezes! After all, the macro needs to check 1048576 empty cells, and this takes time. :-) Just add the first executable line to the procedure - If Application.WorksheetFunction.CountA(Target) < 1 Then Exit Sub
    – JohnSUN
    Commented Sep 13, 2022 at 11:33
  • 1
    Thanks a lot! Now it works perfect! As far as I understand can also use: Set Target = Application.Intersect(Target, Me.Columns(1), Me.UsedRange) If Target Is Nothing Then Exit Sub
    – moninah
    Commented Sep 13, 2022 at 11:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .