Is there a limit to this macro? I made the following modifications and I
receive the following message:

Run-time error '1004':
Method 'Range' of object'_Worksheet' failed

Please note that I have not included all of the code due to limitations
within the forum.

If there is a limit to this macro, that is fine as I will need to inform
management that this is a limitation of excel.

I hope this is the last question as you all have been very helpful.

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,
Range("I37,I39,I40,I41,I42,I43,I44,I45,I59,I60,I74 ,I75,I76,I77,I92,I93,I94,I95,I96,I111,I112,I113,I1 14,I115,I116,I132,I133,I134,I144,I149,I150,I151,I1 67,I168,I169,I171,I172,I173,I174,I175,I176,I177,I1 78,I356,I357,I358,I359,I360,I376,I377,I378,I379,I3 80,I397,I399,I405,I406,I407,I408,I409,I424,I425,I4 26,I436,I441,I442,I443,I444,I445,I446,I447,I448,I4 49,I450")) Is Nothing Then Exit Sub

If Target.Address = "$I$37" Then
If Target.Value = 1 Then

Range("P37").Formula = Range("DM37").Formula
Range("AY37").Formula = Range("DM37").Formula
ElseIf Target.Value = 0 Then
Range("O37").Formula = Range("DL37").Formula
Range("AX37").Formula = Range("DL37").Formula
Range("P37").Formula = Range("DN37").Formula
Range("AY37").Formula = Range("DN37").Formula
End If
End If

If Target.Address = "$I$39" Then

If Target.Value = 1 Then
Range("P39").Formula = Range("DM39").Formula
Range("AY39").Formula = Range("DM39").Formula
ElseIf Target.Value = 0 Then
Range("O39").Formula = Range("DL39").Formula
Range("AX39").Formula = Range("DL39").Formula
Range("P39").Formula = Range("DN39").Formula
Range("AY39").Formula = Range("DN39").Formula
End If
End If

If Target.Address = "$I$40" Then

If Target.Value = 1 Then
Range("P40").Formula = Range("DM40").Formula
Range("AY40").Formula = Range("DM40").Formula
ElseIf Target.Value = 0 Then
Range("O40").Formula = Range("DL40").Formula
Range("AX40").Formula = Range("DL40").Formula
Range("P40").Formula = Range("DN40").Formula
Range("AY40").Formula = Range("DN40").Formula
End If
End If

If Target.Address = "$I$41" Then

If Target.Value = 1 Then
Range("P41").Formula = Range("DM41").Formula
Range("AY41").Formula = Range("DM41").Formula
ElseIf Target.Value = 0 Then
Range("O41").Formula = Range("DL41").Formula
Range("AX41").Formula = Range("DL41").Formula
Range("P41").Formula = Range("DN41").Formula
Range("AY41").Formula = Range("DN41").Formula
End If
End If

If Target.Address = "$I$42" Then

If Target.Value = 1 Then
Range("P42").Formula = Range("DM42").Formula
Range("AY42").Formula = Range("DM42").Formula
ElseIf Target.Value = 0 Then
Range("O42").Formula = Range("DL42").Formula
Range("AX42").Formula = Range("DL42").Formula
Range("P42").Formula = Range("DN42").Formula
Range("AY42").Formula = Range("DN42").Formula
End If
End If

If Target.Address = "$I$43" Then

If Target.Value = 1 Then
Range("P43").Formula = Range("DM43").Formula
Range("AY43").Formula = Range("DM43").Formula
ElseIf Target.Value = 0 Then
Range("O43").Formula = Range("DL43").Formula
Range("AX43").Formula = Range("DL43").Formula
Range("P43").Formula = Range("DN43").Formula
Range("AY43").Formula = Range("DN43").Formula
End If
End If

If Target.Address = "$I$44" Then

If Target.Value = 1 Then
Range("P44").Formula = Range("DM44").Formula
Range("AY44").Formula = Range("DM44").Formula
ElseIf Target.Value = 0 Then
Range("O44").Formula = Range("DL44").Formula
Range("AX44").Formula = Range("DL44").Formula
Range("P44").Formula = Range("DN44").Formula
Range("AY44").Formula = Range("DN44").Formula
End If
End If

..
..
..
..
..
..
..
..
..
End Sub


"jkramos2005" wrote:

Sorry my message was probably confusing. Please see below. I am trying to
the the following, but without success.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$I$37" Then Exit Sub
If Target.Value = 1 Then
Range("P37").Formula = Range("DM37").Formula
Range("AY37").Formula = Range("DM37").Formula
ElseIf Target.Value = 0 Then
Range("O37").Formula = Range("DL37").Formula
Range("AX37").Formula = Range("DL37").Formula
Range("P37").Formula = Range("DN37").Formula
Range("AY37").Formula = Range("DN37").Formula
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$I$39" Then Exit Sub
If Target.Value = 1 Then
Range("P39").Formula = Range("DM39").Formula
Range("AY39").Formula = Range("DM39").Formula
ElseIf Target.Value = 0 Then
Range("O39").Formula = Range("DL39").Formula
Range("AX39").Formula = Range("DL39").Formula
Range("P39").Formula = Range("DN39").Formula
Range("AY39").Formula = Range("DN39").Formula
End If
End Sub

I am trying to apply the logic you had first helped me out with to multiple
lines. For the cells in line 37, please look at cell $I$37, for the cells in
line 39, please look at cell $I$39, and so on and so forth.

Can one have multiple " Private Sub Worksheet_Change(ByVal Target As
Range) " statements?

Thanks


"Mike H" wrote:

Hi,

Not tested but it should be as simple as

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$I$37" Then Exit Sub
If Target.Value = 1 Then
Range("P37").Formula = Range("DM37").Formula
Range("AY37").Formula = Range("DM37").Formula
Range("AY39").Formula = Range("DM39").Formula 'new line

ElseIf Target.Value = 0 Then
Range("O37").Formula = Range("DL37").Formula
Range("AX37").Formula = Range("DL37").Formula
Range("P37").Formula = Range("DN37").Formula
Range("AY37").Formula = Range("DN37").Formula
Range("AY39").Formula = Range("DN39").Formula 'new line

End If
End Sub


Mike
"jkramos2005" wrote:

Mike H,

As such, management wants to add more to this. If I wanted to add extra
rows, to this statement, how would I do that?

I need to do the same thing that I did in row 37 but now for row 39.

Is it simple to do that?

Thanks,

Jeff R

"Mike H" wrote:

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$I$37" Then Exit Sub
If Target.Value = 1 Then
Range("P37").Formula = Range("DM37").Formula
Range("AY37").Formula = Range("DM37").Formula
ElseIf Target.Value = 0 Then
Range("O37").Formula = Range("DL37").Formula
Range("AX37").Formula = Range("DL37").Formula
Range("P37").Formula = Range("DN37").Formula
Range("AY37").Formula = Range("DN37").Formula
End If
End Sub

Mike

"jkramos2005" wrote:

Help!!! I am trying to write something that is referred to as a "change
macro" statement. In my excel worksheet based on what I enter into one cell
(either "0" or "1"), I would like for excel to take formulas that I have
hidden way to the right side of the spreadsheet and copy the appropriate
formula whether it be "0" or "1".

Below is the working statements that I have written:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$37"=1 Then "$P$37"="$DM$37" Then "$AY$37"="$DM$37"
If Target.Address = "$I$37"=0 Then "$O$37"="$DL$37" Then "$AX$37"="$DL$37"
Then "$P$37"="$DN$37" Then "$AY$37"="$DN$37"


End Sub

I then get a Microsoft Visual Basic Compile Error: Syntax Error

Any guidance would be most apppreicated.

Thanks!