Solved: Run Excel Macros IF...

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
Joined
Jul 25, 2004
Messages
5,458
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top