Worksheet change event to Formulate Cells for multiple Ranges

[trickyricky]

How would one go about setting this up so that if I enter a number in the L column, columns M and N in the same row will automatically be formulas. Likewise if I enter a number in the M column, columns L and N for that particular row will automatically change to formulas.

For example in detail I enter the value $0.25 in cell L7. The formula in M7 should be "=IF(L7<>"",L7*$L$2,"")", and N7 "=IF(M7<>"",M7/$M$4,0)".
If I enter the value $50 in say cell M8, the formula in L8 should be "=IF(M8<>"",M8/$L$2,"")", and N8 "=IF(M8<>"",M8/$M$4,0)".
and finally..
If I enter the value 5% in cell N7. The formula in L7 should be "=IF(M7<>"",M7/$L$2,"")", and M7 "=IF(N7<>"",N7*$M$4,"")".

I need the change events to work relatively within each row. From Rows 7 to 13.

I've attached a worksheet to kind of show what I'm looking at.

Thanks in advance I hope I was clear enough!

[Mallycat]

Try This

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exit_here
    If Intersect(Target, Range("L7:N13")) Then
        If IsNumeric(Target.Formula) Then
            Select Case Target.Column
                Case Is = 12
                        Cells(Target.Row, 13).Formula = "=IF(" & Target.Address & "<>" & Chr(34) & Chr(34) & "," & Target.Address & "*$L$2," & Chr(34) & Chr(34) & ")"
                        Cells(Target.Row, 14).Formula = "=IF(" & Target.Offset(0, 1).Address & "<>" & Chr(34) & Chr(34) & "," & Target.Offset(0, 1).Address & "/$M$4,0)"
                Case Is = 13
                        Cells(Target.Row, 12).Formula = "=IF(" & Target.Address & "<>" & Chr(34) & Chr(34) & "," & Target.Address & "/$L$2," & Chr(34) & Chr(34) & ")"
                        Cells(Target.Row, 14).Formula = "=IF(" & Target.Address & "<>" & Chr(34) & Chr(34) & "," & Target.Address & "/$M$4,0)"
                Case Is = 14
                        Cells(Target.Row, 12).Formula = "=IF(" & Target.Offset(0, -1).Address & "<>" & Chr(34) & Chr(34) & "," & Target.Offset(0, -1).Address & "/$L$2," & Chr(34) & Chr(34) & ")"
                        Cells(Target.Row, 13).Formula = "=IF(" & Target.Address & "<>" & Chr(34) & Chr(34) & "," & Target.Address & "*$M$4," & Chr(34) & Chr(34) & ")"
            End Select
        End If
    End If
exit_here:
End Sub

edit: slight simplification of code

[Winon]

@ Mallycat,

Nice work Mallycat! Waht is?

Chr(34)

[Mallycat]

Chr(34) is the code to create a quote mark "

When building a formula in VBA that you want to add to a cell in a sheet, you can't add " in the formula, as this is used by the VBA code itself, so you have to use chr(34) everywhere you want a " to appear in the final formula.

[Winon]

@ Mallycat,

Thank you for clarifying that for me, however, I have changed it with the ", and it works fine! I think you could do this quite safely when one uses the Ampersand. It might be an entirely different case when a " is required without the Ampersand.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo exit_here
    If Intersect(Target, Range("L7:N13")) Then
        Select Case Target.Column
            Case Is = 12
                If IsNumeric(Target.Formula) Then
                    Cells(Target.Row, 13).Formula = "=IF(" & Target.Address & "<>" & " & " & "," & Target.Address & "*$L$2," & " & " & ")"
                    Cells(Target.Row, 14).Formula = "=IF(" & Target.Offset(0, 1).Address & "<>" & " & " & "," & Target.Offset(0, 1).Address & "/$M$4,0)"
                End If
            Case Is = 13
                If IsNumeric(Target.Formula) Then
                    Cells(Target.Row, 12).Formula = "=IF(" & Target.Address & "<>" & " & " & "," & Target.Address & "/$L$2," & " & " & ")"
                    Cells(Target.Row, 14).Formula = "=IF(" & Target.Address & "<>" & " & " & "," & Target.Address & "/$M$4,0)"
                End If
            Case Is = 14
                If IsNumeric(Target.Formula) Then
                    Cells(Target.Row, 12).Formula = "=IF(" & Target.Offset(0, -1).Address & "<>" & " & " & "," & Target.Offset(0, -1).Address & "/$L$2," & " & " & ")"
                    Cells(Target.Row, 13).Formula = "=IF(" & Target.Address & "<>" & " & " & "," & Target.Address & "*$M$4," & " & " & ")"
                End If
        End Select
    End If
exit_here:
End Sub

[Mallycat]

Thanks. I didn't know that.

[trickyricky]

Man you guys are great thanks for all your help! Mallycat's version works perfect you are GENIUS! When I tried Winon's version of the code I couldn't get it to work though? Winon are you sure your code worked for you maybe I'm doing something wrong?

Either way I appreciate both of you thanks!

[Mallycat]

Hey trickyricky. I have lots of friends in the USA. None in Kansas though, but I could always click the heels of my red shoes together, and wish for something special. :-)

[Winon]

I am so sorry guys,

Mallycat's Code is great, but the Char(34) could be replaced with """ instead of just "

I checked out your concern trickyricky and, yes, you are right. The amended Code should look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
     On Error GoTo exit_here
    If Intersect(Target, Range("L7:N13")) Then
        If IsNumeric(Target.Formula) Then
            Select Case Target.Column
                Case Is = 12
                        Cells(Target.Row, 13).Formula = "=IF(" & Target.Address & "<>" & """ & """ & "," & Target.Address & "*$L$2," & """ & """ & ")"
                        Cells(Target.Row, 14).Formula = "=IF(" & Target.Offset(0, 1).Address & "<>" & """ & """ & "," & Target.Offset(0, 1).Address & "/$M$4,0)"
                Case Is = 13
                        Cells(Target.Row, 12).Formula = "=IF(" & Target.Address & "<>" & """ & """ & "," & Target.Address & "/$L$2," & """ & """ & ")"
                        Cells(Target.Row, 14).Formula = "=IF(" & Target.Address & "<>" & """ & """ & "," & Target.Address & "/$M$4,0)"
                Case Is = 14
                        Cells(Target.Row, 12).Formula = "=IF(" & Target.Offset(0, -1).Address & "<>" & """ & """ & "," & Target.Offset(0, -1).Address & "/$L$2," & """ & """ & ")"
                        Cells(Target.Row, 13).Formula = "=IF(" & Target.Address & "<>" & """ & """ & "," & Target.Address & "*$M$4," & """ & """ & ")"
            End Select
        End If
    End If
exit_here:

End Sub