View original (French)

If not Intersect on multiple columns

Solved
touroul -  
Good evening forum!

In a macro, I want to trigger a command only in certain columns.
Here in example, columns A and B, the command is a Msgbox. 

Sub Mamacro()
 Dim rng As Range
 With ActiveSheet
 Set rng = Union(Columns("A:A"), Columns("C:C"))
 If Not Intersect(Target, rng) Is Nothing Then
 Msgbox("Match")
 End If
 End With
 End Sub


But Excel shows its disagreement with a nasty 'Error 424, object required."

Can I ask you for some help with my error please?
Thank you in advance

Configuration: Windows / Excel 365

4 answers

  2.  via55
    Good evening touroul

    I don't understand the purpose of your If ...
    There is no need for a condition; if you want to replace the dots only in columns A and C, you just need to select them with Union and then apply the replacement to the selection 
     Union(Columns("A:A"), Columns("C:C")).Select Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    1. touroul Author
      Hello Le Pivert

      Thank you for your reply.
      I fixed my misplaced quotes.
      However, it's still not right.

      My macro is triggered by a button and not by Worksheet_SelectionChange.
      This is what it looks like: (in fact, the macro replaces periods with commas in the relevant columns): 

      Sub Mamacro() 
       With ActiveSheet
       If Not Application.Intersect(Target, Range("A:A,C:C")) Is Nothing Then
       Cells.Replace What:=".", Replacement:=".", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
       End If
       End With
       End Sub


      Still 'Error 424, object required'

      Thanks for the next steps :)
      1. touroul Author
        Good evening Via

        Yes and yes! Sorry, I went too far, I couldn't look at it simply.
        That was indeed the objective.

        Thank you very much to you and LePivert for your help.
        Have a good evening.