Macro intiated from a cell, how to improve the code for not getting an error

[amitcohen]

Hi Guys

I'm using the following code to start a little macro that when clicking on a cell, it will populate the value in another cell.

The problem I'm facing is that I need over 1200 cells with this code (it's divided to 3 columns, 400 cells in a column)
And while creating a code for each and every cell, Excel giving me an error, saying the code is to large to execute.

Is there a way to improve the code, so I can use it on a large scale?

Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' N Column

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N1")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N1")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N2")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N2")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N3")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N3")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N4")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N4")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N5")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N5")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N6")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N6")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N7")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N7")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N8")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N8")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N9")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N9")
    End If
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N10")) Is Nothing Then
       Range("L2").Select
    ActiveCell.FormulaR1C1 = Range("N10")
    End If
End Sub

This code needed for Columns N, O, P.
400 cells each. ("N1:N400") etc'

Thank you in advance for your kind help

Amit

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Or Intersect(Target, Range("N1:P400")) Is Nothing Then Exit Sub
Target.Offset(, -2).Formula = "=" & Target.Address
End Sub

 

[amitcohen]

Hi VoG
Thanks for a quick replay

I (we) almost there..

You definably sort out the 'large code' error.
Yet there is one things that came out as a result.

The offset (, -2) is actually put the value next to the cell I'm choosing.
This unfortunately cosign an overwriting cells
for instance:
when clicking on call "O11" it will overwrite the cell "N13"
Since column "N" is part of the code, I wont be able to use it as a result.

Each column (N, O, P) has its own private cell to populate.
Lets say any chosen cell in "N" column will populate call "L2" only,
any chosen cell from column "O" will populate cell "L3" only,
and cells from "P" will populate "L4" only.

I was trying to play around with the code you gave me, yet with no success..

Any suggestions?

Thanks
Amit

 

[VoG]

If it is always column L then perhaps

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Or Intersect(Target, Range("N1:P400")) Is Nothing Then Exit Sub
Range("L" & Target.Row).Formula = "=" & Target.Address
End Sub

 

[amitcohen]

Hi VoG
Thanks so much for your help!!
Problem solved.

Amit