1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Run Excel Macros IF...

Discussion in 'Business Applications' started by bapcki1, Nov 12, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. bapcki1

    bapcki1 Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    5
    Hi!

    I desperately need some help with some excel macros, can anybody help please?

    I need the VBA code so that an excel macros will run once automatically when, for example cell A4 = 1.

    Using a great thread posted by Zack Barresse, I've managed to get this far with the VBA:

    Sub copytohere()
    If ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "1" Then
    Range("A1").Select
    Selection.Copy
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    End If
    End Sub

    I dont think I've got it quite right though...
    Can anyone help please? :)

    This macros is basic - all it does is copy data from cell A1 to E1 and then deletes A1.
    I was hoping the IF statement would make the macros run once automatically when cell A4 = 1 however when I enter 1 into cell A4 nothing happens.

    What is interesting is that if i then go to run the macros manually, it will perform the macros correctly if cell A4 = 1 .If cell A4 doesn't equal 1 and I run the macros manually, nothing happens.

    I need the VBA so that the macros will run once automatically when cell A4 equals 1.

    Any help si much appreciated.

    Thank you!


    Paddy
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    If you want something to occur "automatically", you'll need to utilize an event. There are different events you can 'capture' or 'hook' in Excel, as it's commonly referred to. What it sounds like to me that you will need is either a change event or a calculate event.

    The change event will fire every time a cell is changed. So if a user starts typing in a cell and hits the Esc key, a change event will not have occured, as opposed to them hitting Enter, then a change event has occured.

    The calculate event will run every time the worksheet is calculated, regardless of a sheet change. This may be beneficial if A1 houses a formula which will update from other cells within the workbook.

    I'm going to assume you want to utilize a calculate event, but you can use a change event if you want. I'll post both code snippets, so choose the one you want. Both of these are worksheet module code events. To put there, right click your worksheet tab, select View Code.

    Code:
    Private Sub Worksheet_Calculate()
        If Me.Range("A4").Value = "1" Then
            Application.EnableEvents = False
            Me.Range("E1").Value = Me.Range("A1").Value
            Me.Range("A1").ClearContents
            Application.EnableEvents = True
        End If
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Me.Range("A4").Value = "1" Then
            Application.EnableEvents = False
            Me.Range("E1").Value = Me.Range("A1").Value
            Me.Range("A1").ClearContents
            Application.EnableEvents = True
        End If
    End Sub
    One thing I'm not sure about is what about the value in A4 after the routine is run? When it fires again (either when another cell is changed, or another calculate event is fired) the cell value in E1 will be cleared, because A1 was cleared the last time. My question is then wouldn't you want to change A4 afterwards so you wouldn't just be clearing the cell again? It would help to know what is housed in A4 as well.

    HTH
     
  3. bapcki1

    bapcki1 Thread Starter

    Joined:
    Nov 12, 2011
    Messages:
    5
    YES!!

    Thank you so much for your time and help Zack!
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    No problem. :) Can you mark your thread as Solved?
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026536

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice