Forum Discussion

HotBlue's avatar
HotBlue
Copper Contributor
Jul 30, 2020
Solved

Multiple actions of an IF test

Is there a way to implement 2 actions based on the results of an IF test?  For example:

IF($G6=$K6,...  for a true result I would like to implement both copying $G6 to $M6 and set $K6="".

I have tried IF($G6=$K6,$M6;$K6="") but it gives me an error.

Any possible suggestions?

 

 

excel
  • HansVogelaar's avatar
    HansVogelaar
    Jul 30, 2020

    HotBlue 

    This is not possible with a formula. A formula returns a value in the cell that contains the formula; it cannot perform other actions. What you want requires VBA:

    • Right-click the sheet tab.
    • Select 'View Code' from the context menu.
    • Copy the code listed below into the worksheet module.
    • Close the Visual Basic Editor.
    • Save the workbook as a macro-enabled workbook (.xlsm).
    • Make sure that you allow macros when you open the workbook.

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("G6,K6"), Target) Is Nothing Then
        If Range("G6").Value = Range("K6").Value Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Range("M6").Value = Range("G6").Value
            Range("K6").ClearContents
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
End Sub

4 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 30, 2020

    HotBlue  as already noted, a formula can only change the value of the cell it is in.  Your desire to change K6 makes me think you might want to rethink how you have the worksheet set up.  If K6 is a formula itself then you can just update that formula to include this condition.  But if K6 is an input, it is better practice to not change it but rather separate input data and output / formatted display data so you have 'input data' on 1 page and then you can have the 'output data' on another sheet that is all formatted and adjusted the way you want it to look.

  • wumolad's avatar
    wumolad
    Iron Contributor
    Jul 30, 2020

    Hi HotBlue 

     

    I believe what you can do is to type in the formula in M6, so that M6 gets the value of G6 when G6 is equal to K6.

     

    In which case, this formula will suffice.

     

    IF($G6=$K6,$G6,"")

     

    This assumes you want M6 value to be blank if G6 is not equal to K6.

     

    However, the second aspect is dicey as I am not sure you intend to type in any formula in K6, given that K6 is part of the logical test in the IF test. In this case, you might need to use VBA to resolve this.

     

    Cheers

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 30, 2020

    HotBlue 

    This is not possible with a formula. A formula returns a value in the cell that contains the formula; it cannot perform other actions. What you want requires VBA:

    • Right-click the sheet tab.
    • Select 'View Code' from the context menu.
    • Copy the code listed below into the worksheet module.
    • Close the Visual Basic Editor.
    • Save the workbook as a macro-enabled workbook (.xlsm).
    • Make sure that you allow macros when you open the workbook.

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("G6,K6"), Target) Is Nothing Then
        If Range("G6").Value = Range("K6").Value Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Range("M6").Value = Range("G6").Value
            Range("K6").ClearContents
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
End Sub

Resources