| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can the last two if statements be combined together?
If the user change c4, i want the value of c39 to change or
If the user change e4, i want the value of e39 to change Is there a better way?? Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("c4", "e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Application.EnableAutoComplete = False End If If Intersect(Range("c4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("c39").Value = Range("c4").Value End If If Intersect(Range("e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("e39").Value = Range("e4").Value End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can the last two if statements be combined together?
This will work. It uses binary arithmetic.
Sub Worksheet_SelectionChange(ByVal Target As Range) Results = 0 if not Intersect(Range("c4"), Target) is nothing then Results = Results + 1 end if if not Intersect(Range("E4"), Target) is nothing then Results = Results + 2 end if select Case Results case 0: Application.EnableAutoComplete = True case 1: Application.EnableAutoComplete = True Range("c39").Value = Range("c4").Value case 2: Application.EnableAutoComplete = True Range("e39").Value = Range("e4").Value case 3: Application.EnableAutoComplete = False Range("c39").Value = Range("c4").Value Range("e39").Value = Range("e4").Value end select end sub "Brad" wrote: If the user change c4, i want the value of c39 to change or If the user change e4, i want the value of e39 to change Is there a better way?? Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("c4", "e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Application.EnableAutoComplete = False End If If Intersect(Range("c4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("c39").Value = Range("c4").Value End If If Intersect(Range("e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("e39").Value = Range("e4").Value End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can the last two if statements be combined together?
Brad: I forgot to mention one item. if somebody copies a range of cells and
pastes it on over both cells, both cells will change. "joel" wrote: This will work. It uses binary arithmetic. Sub Worksheet_SelectionChange(ByVal Target As Range) Results = 0 if not Intersect(Range("c4"), Target) is nothing then Results = Results + 1 end if if not Intersect(Range("E4"), Target) is nothing then Results = Results + 2 end if select Case Results case 0: Application.EnableAutoComplete = True case 1: Application.EnableAutoComplete = True Range("c39").Value = Range("c4").Value case 2: Application.EnableAutoComplete = True Range("e39").Value = Range("e4").Value case 3: Application.EnableAutoComplete = False Range("c39").Value = Range("c4").Value Range("e39").Value = Range("e4").Value end select end sub "Brad" wrote: If the user change c4, i want the value of c39 to change or If the user change e4, i want the value of e39 to change Is there a better way?? Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("c4", "e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Application.EnableAutoComplete = False End If If Intersect(Range("c4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("c39").Value = Range("c4").Value End If If Intersect(Range("e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("e39").Value = Range("e4").Value End If End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can the last two if statements be combined together?
If I read what you are doing correctly, you should be able to use this code
in place of what you posted... Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("C4,E4"), Target) Is Nothing Then Application.EnableAutoComplete = False Cells(39, Target.Column).Value = Target.Value Application.EnableAutoComplete = True End If End Sub Notice that I changed the Range you used in the Intersect function call from ("C4","E4") to ("C4,E4")... the range you used included the cells C4, D4 and E4 whereas your subsequent code seemed to indicate that you don't care about Target being D4. -- Rick (MVP - Excel) "Brad" wrote in message ... If the user change c4, i want the value of c39 to change or If the user change e4, i want the value of e39 to change Is there a better way?? Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("c4", "e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Application.EnableAutoComplete = False End If If Intersect(Range("c4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("c39").Value = Range("c4").Value End If If Intersect(Range("e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("e39").Value = Range("e4").Value End If End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can the last two if statements be combined together?
For case 1 and 2 should not the application.EnableAutoComplete be False? "joel" wrote: This will work. It uses binary arithmetic. Sub Worksheet_SelectionChange(ByVal Target As Range) Results = 0 if not Intersect(Range("c4"), Target) is nothing then Results = Results + 1 end if if not Intersect(Range("E4"), Target) is nothing then Results = Results + 2 end if select Case Results case 0: Application.EnableAutoComplete = True case 1: Application.EnableAutoComplete = True Range("c39").Value = Range("c4").Value case 2: Application.EnableAutoComplete = True Range("e39").Value = Range("e4").Value case 3: Application.EnableAutoComplete = False Range("c39").Value = Range("c4").Value Range("e39").Value = Range("e4").Value end select end sub "Brad" wrote: If the user change c4, i want the value of c39 to change or If the user change e4, i want the value of e39 to change Is there a better way?? Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("c4", "e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Application.EnableAutoComplete = False End If If Intersect(Range("c4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("c39").Value = Range("c4").Value End If If Intersect(Range("e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("e39").Value = Range("e4").Value End If End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can the last two if statements be combined together?
Cells(39, Target.Column).Value = Target.Value
You could also use this line instead of the one above (they will both work the same)... Target.Offset(35).Value = Target.Value -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If I read what you are doing correctly, you should be able to use this code in place of what you posted... Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Range("C4,E4"), Target) Is Nothing Then Application.EnableAutoComplete = False Cells(39, Target.Column).Value = Target.Value Application.EnableAutoComplete = True End If End Sub Notice that I changed the Range you used in the Intersect function call from ("C4","E4") to ("C4,E4")... the range you used included the cells C4, D4 and E4 whereas your subsequent code seemed to indicate that you don't care about Target being D4. -- Rick (MVP - Excel) "Brad" wrote in message ... If the user change c4, i want the value of c39 to change or If the user change e4, i want the value of e39 to change Is there a better way?? Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("c4", "e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Application.EnableAutoComplete = False End If If Intersect(Range("c4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("c39").Value = Range("c4").Value End If If Intersect(Range("e4"), Target) Is Nothing Then Application.EnableAutoComplete = True Else Range("e39").Value = Range("e4").Value End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF STATEMENTS COMBINED WITH VLOOKUPS | Excel Worksheet Functions | |||
| Use of Combined IF and AND statements | Excel Discussion (Misc queries) | |||
| Using "If" combined with "AND" statements | New Users to Excel | |||
| Combined Statements | Excel Discussion (Misc queries) | |||
| Conditional formatting combined with multiple IF statements | Excel Worksheet Functions | |||
Linear Mode
