Results 1 to 2 of 2

Thread: VBa automatically run macro if cell changes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBa automatically run macro if cell changes

    Hi all,
    I want to make the spread sheet automatically run this macro code when a number is inputted into column B
    what code do i need to add to it
    Sub Ratio_BC()


    Dim Lastrow As Long


    Application.ScreenUpdating = False


    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    Range("D2:D" & Lastrow).Formula = "=IF(ISNUMBER(--MID($B:B,SEARCH(""241"",$B:B),3)),MID($B:B,18,10))"


    Application.ScreenUpdating = True


    End Sub

    thanks

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,392
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBa automatically run macro if cell changes

    Try this event macro (i.e. right click on the tab you want the code to run on and from the short cut menu select View Code and paste the following code) on the sheet in question:

    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim lngLastRow As Long
    
        If Target.Column = 2 Then
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
                    lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
                    Range("D2:D" & lngLastRow).Formula = "=IF(ISNUMBER(--MID($B:B,SEARCH(""241"",$B:B),3)),MID($B:B,18,10))"
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        End If
    
    End Sub
    Have you tested the formula as I'm I don't think SEARCH or MID don't work for entire columns?

    Robert

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •