How to write a "change macro"

  • Thread starter Thread starter jkramos2005
  • Start date Start date
J

jkramos2005

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!
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$37" Then

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


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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
 
Thank you so much for the help. I knew I was totally off base with the
language. My first crack at a macro...

I really do appreciate the quick response.
 
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
 
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
 
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
 
You can only have 1 worksheet change event per worksheet so you have to
combine them

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I37,I39")) 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

End Sub
 
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,I114,I115,I116,I132,I133,I134,I144,I149,I150,I151,I167,I168,I169,I171,I172,I173,I174,I175,I176,I177,I178,I356,I357,I358,I359,I360,I376,I377,I378,I379,I380,I397,I399,I405,I406,I407,I408,I409,I424,I425,I426,I436,I441,I442,I443,I444,I445,I446,I447,I448,I449,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
 
Back
Top