SOLVED

Multiple actions of an IF test

%3CLINGO-SUB%20id%3D%22lingo-sub-1555445%22%20slang%3D%22en-US%22%3EMultiple%20actions%20of%20an%20IF%20test%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555445%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20implement%202%20actions%20based%20on%20the%20results%20of%20an%20IF%20test%3F%26nbsp%3B%20For%20example%3A%3C%2FP%3E%3CP%3EIF(%24G6%3D%24K6%2C...%26nbsp%3B%20for%20a%20true%20result%20I%20would%20like%20to%20implement%20both%20copying%20%24G6%20to%20%24M6%20and%20set%20%24K6%3D%22%22.%3C%2FP%3E%3CP%3EI%20have%20tried%20IF(%24G6%3D%24K6%2C%24M6%3B%24K6%3D%22%22)%20but%20it%20gives%20me%20an%20error.%3C%2FP%3E%3CP%3EAny%20possible%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1555445%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555913%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20actions%20of%20an%20IF%20test%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555913%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744621%22%20target%3D%22_blank%22%3E%40HotBlue%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20not%20possible%20with%20a%20formula.%20A%20formula%20returns%20a%20value%20in%20the%20cell%20that%20contains%20the%20formula%3B%20it%20cannot%20perform%20other%20actions.%20What%20you%20want%20requires%20VBA%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ERight-click%20the%20sheet%20tab.%3C%2FLI%3E%0A%3CLI%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FLI%3E%0A%3CLI%3ECopy%20the%20code%20listed%20below%20into%20the%20worksheet%20module.%3C%2FLI%3E%0A%3CLI%3EClose%20the%20Visual%20Basic%20Editor.%3C%2FLI%3E%0A%3CLI%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FLI%3E%0A%3CLI%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20the%20workbook.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Not%20Intersect(Range(%22G6%2CK6%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20If%20Range(%22G6%22).Value%20%3D%20Range(%22K6%22).Value%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22M6%22).Value%20%3D%20Range(%22G6%22).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22K6%22).ClearContents%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555916%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20actions%20of%20an%20IF%20test%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555916%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744621%22%20target%3D%22_blank%22%3E%40HotBlue%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20what%20you%20can%20do%20is%20to%20type%20in%20the%20formula%20in%20M6%2C%20so%20that%20M6%20gets%20the%20value%20of%20G6%20when%20G6%20is%20equal%20to%20K6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20which%20case%2C%20this%20formula%20will%20suffice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EIF(%24G6%3D%24K6%2C%24G6%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20assumes%20you%20want%20M6%20value%20to%20be%20blank%20if%20G6%20is%20not%20equal%20to%20K6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20second%20aspect%20is%20dicey%20as%20I%20am%20not%20sure%20you%20intend%20to%20type%20in%20any%20formula%20in%20K6%2C%20given%20that%20K6%20is%20part%20of%20the%20logical%20test%20in%20the%20IF%20test.%20In%20this%20case%2C%20you%20might%20need%20to%20use%20VBA%20to%20resolve%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1556121%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20actions%20of%20an%20IF%20test%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1556121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744621%22%20target%3D%22_blank%22%3E%40HotBlue%3C%2FA%3E%26nbsp%3B%20as%20already%20noted%2C%20a%20formula%20can%20only%20change%20the%20value%20of%20the%20cell%20it%20is%20in.%26nbsp%3B%20Your%20desire%20to%20change%20K6%20makes%20me%20think%20you%20might%20want%20to%20rethink%20how%20you%20have%20the%20worksheet%20set%20up.%26nbsp%3B%20If%20K6%20is%20a%20formula%20itself%20then%20you%20can%20just%20update%20that%20formula%20to%20include%20this%20condition.%26nbsp%3B%20But%20if%20K6%20is%20an%20input%2C%20it%20is%20better%20practice%20to%20not%20change%20it%20but%20rather%20separate%20input%20data%20and%20output%20%2F%20formatted%20display%20data%20so%20you%20have%20'input%20data'%20on%201%20page%20and%20then%20you%20can%20have%20the%20'output%20data'%20on%20another%20sheet%20that%20is%20all%20formatted%20and%20adjusted%20the%20way%20you%20want%20it%20to%20look.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1557337%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20actions%20of%20an%20IF%20test%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1557337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20all%20for%20the%20good%20advice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHotBlue%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

 

 

4 Replies
Highlighted
Best Response confirmed by HotBlue (New Contributor)
Solution

@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
Highlighted

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

 

Highlighted

@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.

Highlighted

@mtarler 

 

Thank you all for the good advice.

 

HotBlue