| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to write a "change macro"
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
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) "jkramos2005" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
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" 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
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 "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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to write a "change macro"
Thank you so much for the help everyone!!!!!
"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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I auto transform sets of data? (change "female" to "f") | Excel Discussion (Misc queries) | |||
| How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
| =IF(D13="PAID","YES","NO") Can I change fonts colour | Excel Discussion (Misc queries) | |||
Hybrid Mode
