I’m trying to copy the value ‘10’ into column ‘L’, but it only works when I type it in manually. When I attempt to copy and paste, it doesn’t work.

Question

I’m trying to copy the value ‘10’ into column ‘L’, but it only works when I type it in manually. When I attempt to copy and paste, it doesn’t work.

suraj sharma 85

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim rng As Range
    Dim temp As Variant

    ' Set your worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Check if the change is made in column L and the cell is not empty
    If Target.Column = 12 And Not IsEmpty(Target) Then

        ' Find matching value in column L
        Set rng = ws.Columns("L").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)

        ' If a match is found and it's not the same cell where the change was made
        If Not rng Is Nothing And rng.Address <> Target.Address Then

            ' Ignore the change and exit the subroutine
            Exit Sub

        Else

            ' Find matching value in column E
            Set rng = ws.Columns("E").Find(Target.Value)

            ' If a match is found
            If Not rng Is Nothing Then

                ' Store the row from column A to H of the matching row in a temporary variable
                temp = ws.Range("A" & rng.Row & ":H" & rng.Row).Value

                ' Copy the row from column A to H to the matching row in column L
                ws.Range("A" & rng.Row & ":H" & rng.Row).Value = ws.Range("A" & Target.Row & ":H" & Target.Row).Value

                ' Move the stored row to the original location of the typed row
                ws.Range("A" & Target.Row & ":H" & Target.Row).Value = temp

            End If

        End If
.Row & ":H" & Target.Row).Value = temp

            EndPrivate Sub Worksheet_Change(ByVal Target As Range)

Barry Schwarz 1,151 Reputation points

2023-11-04T03:54:05.35+00:00

The code you posted is missing at least one ENDIF. Please copy and paste the entire module into your message.

It would also help if you showed us sample data the macro is processing when this problem occurs.

You might also consider removing some of the extraneous tags you attached to the question.

suraj sharma 85

hi @Barry Schwarz thanks for support i made some changes below it looks it working now i tried below code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim rng As Range
    Dim temp As Variant
    Dim cell As Range

    ' Turn off automatic calculations and screen updating
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    ' Set your worksheet
    Set ws = ThisWorkbook.Sheets("CGC (A.T)")

    ' Loop through each cell in the target range
    For Each cell In Target
        ' Check if the change is made in column L and the cell is not empty
        If cell.Column = 12 And Not IsEmpty(cell) Then

            ' Find matching value in column L
            Set rng = ws.Columns("L").Find(cell.Value, LookIn:=xlValues, lookat:=xlWhole)

            ' If a match is found and it's not the same cell where the change was made
            If Not rng Is Nothing And rng.Address <> cell.Address Then

                ' Ignore the change and exit the subroutine
                Exit Sub

            Else

                ' Find matching value in column E
                Set rng = ws.Columns("E").Find(cell.Value)

                ' If a match is found
                If Not rng Is Nothing Then

                    ' Store the row from column A to H of the matching row in a temporary variable
                    temp = ws.Range("A" & rng.Row & ":H" & rng.Row).Value

                    ' Copy the row from column A to H to the matching row in column L
                    ws.Range("A" & rng.Row & ":H" & rng.Row).Value = ws.Range("A" & cell.Row & ":H" & cell.Row).Value

                    ' Move the stored row to the original location of the typed row
                    ws.Range("A" & cell.Row & ":H" & cell.Row).Value = temp

                End If

            End If

        End If
    Next cell

    ' Turn on automatic calculations and screen updating
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Barry Schwarz 1,151 Reputation points

2023-11-04T20:25:51.7433333+00:00
Some of your code seems a little strange.

You process every cell in the Target range but you ignore any not in column L. It would seem more efficient to process only the cells in the Target range that are also in column L.

If there are duplicate values in column L or Target, you stop the entire process as soon as you encounter the first duplicate.

When you find a match between columns E and L, you swap the values in columns A to H of the two rows. This is completely unrelated to your stated subject of copying the value 10 into a cell.

Barry Schwarz 1,151 Reputation points

2023-11-04T03:54:05.35+00:00

The code you posted is missing at least one ENDIF. Please copy and paste the entire module into your message.

It would also help if you showed us sample data the macro is processing when this problem occurs.

You might also consider removing some of the extraneous tags you attached to the question.
suraj sharma 85 Reputation points

2023-11-04T04:01:30.4033333+00:00

hi @Barry Schwarz thanks for support i made some changes below it looks it working now i tried below code:

Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet Dim rng As Range Dim temp As Variant Dim cell As Range ' Turn off automatic calculations and screen updating Application.Calculation = xlCalculationManual Application.ScreenUpdating = False ' Set your worksheet Set ws = ThisWorkbook.Sheets("CGC (A.T)") ' Loop through each cell in the target range For Each cell In Target ' Check if the change is made in column L and the cell is not empty If cell.Column = 12 And Not IsEmpty(cell) Then ' Find matching value in column L Set rng = ws.Columns("L").Find(cell.Value, LookIn:=xlValues, lookat:=xlWhole) ' If a match is found and it's not the same cell where the change was made If Not rng Is Nothing And rng.Address <> cell.Address Then ' Ignore the change and exit the subroutine Exit Sub Else ' Find matching value in column E Set rng = ws.Columns("E").Find(cell.Value) ' If a match is found If Not rng Is Nothing Then ' Store the row from column A to H of the matching row in a temporary variable temp = ws.Range("A" & rng.Row & ":H" & rng.Row).Value ' Copy the row from column A to H to the matching row in column L ws.Range("A" & rng.Row & ":H" & rng.Row).Value = ws.Range("A" & cell.Row & ":H" & cell.Row).Value ' Move the stored row to the original location of the typed row ws.Range("A" & cell.Row & ":H" & cell.Row).Value = temp End If End If End If Next cell ' Turn on automatic calculations and screen updating Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
Barry Schwarz 1,151 Reputation points

2023-11-04T20:25:51.7433333+00:00

Some of your code seems a little strange.

You process every cell in the Target range but you ignore any not in column L. It would seem more efficient to process only the cells in the Target range that are also in column L.

If there are duplicate values in column L or Target, you stop the entire process as soon as you encounter the first duplicate.

When you find a match between columns E and L, you swap the values in columns A to H of the two rows. This is completely unrelated to your stated subject of copying the value 10 into a cell.

I’m trying to copy the value ‘10’ into column ‘L’, but it only works when I type it in manually. When I attempt to copy and paste, it doesn’t work.

Activity