Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a variable macro when any value is entered into a variable cell
Dear all
I have a simple problem and a difficult problem. Simple: I have two columns in a worksheet. I have written a code for a macro that enters today's date in the second column, then converts it to a value - i.e. not =today(). I want that code to be executed when any value is entered into the first column. e.g. If text is entered in A1 and A1 is clicked away from - B1 is populated with =today(), then copied and pasted into a value (via the macro I have already written) Difficult: This is an extension of the simple problem, but need help creating a more complex macro to solve the below- I have a worksheet that contains an unlimited number of rows. I need a macro to be run to populate Column B with =today(), then copied and pasted into a value, when a value has been added into Column A. However, I need this macro to work for any row within Column A & B. e.g. If text is entered in A1 and A1 is clicked away from - B1 is populated with =today(), then copied and pasted into a value. And this macro will work if text is entered in A100 and A100 is clicked away from - B100 is populated with =today(), then copied and pasted into a value. Can anyone please help with either the above problems??? Kind Regards Iain |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a variable macro when any value is entered into a variable
Hi Iain,
Look at the worksheet_change event - you'll see that when you click away from A1 this event fires and you can then use the event to populate B1. Word of advice - don't use a real email address with newsgroups - you'll get a ton of spam This will work simply for the easy problem and with a bit of lateral thinking it'll work for the difficult one. Cheers, John -- www.alignment-systems.com " wrote: Dear all I have a simple problem and a difficult problem. Simple: I have two columns in a worksheet. I have written a code for a macro that enters today's date in the second column, then converts it to a value - i.e. not =today(). I want that code to be executed when any value is entered into the first column. e.g. If text is entered in A1 and A1 is clicked away from - B1 is populated with =today(), then copied and pasted into a value (via the macro I have already written) Difficult: This is an extension of the simple problem, but need help creating a more complex macro to solve the below- I have a worksheet that contains an unlimited number of rows. I need a macro to be run to populate Column B with =today(), then copied and pasted into a value, when a value has been added into Column A. However, I need this macro to work for any row within Column A & B. e.g. If text is entered in A1 and A1 is clicked away from - B1 is populated with =today(), then copied and pasted into a value. And this macro will work if text is entered in A100 and A100 is clicked away from - B100 is populated with =today(), then copied and pasted into a value. Can anyone please help with either the above problems??? Kind Regards Iain |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a variable macro when any value is entered into a variable
John
I have tried using the worksheet_change event as follow: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column = 3 Then DATA1 End Sub Along with the Macro: Sub DATA1() Range("E9").Select ActiveCell.FormulaR1C1 = "=TODAY()" Range("E9").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F9").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "" Range("F9").Select End Sub However, When I select C9 and click off it, nothing happens. Although E9 should have todays date value in it. Can you see where I am going wrong? I am using Excel 2003, and Column 3(C) is a dropdown list of values. Kind Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a variable macro when any value is entered into a variable
Hi Ian,
Your code works for me. Perhaps events have been inadevertently turned off. To resolve this, in the VBE immediate window type: Application.EnableEvents = True and hit the Enter key. Incidentally, i think that your procedu Sub DATA1() Range("E9").Select ActiveCell.FormulaR1C1 = "=TODAY()" Range("E9").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F9").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "" Range("F9").Select End Sub could be expressed as: '============= Sub DATA1() With Range("E9") .Value = Date .Offset(0, 1).ClearContents .Offset(0, 1).Select End With End Sub '<<============= --- Regards, Norman wrote in message oups.com... John I have tried using the worksheet_change event as follow: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column = 3 Then DATA1 End Sub Along with the Macro: Sub DATA1() Range("E9").Select ActiveCell.FormulaR1C1 = "=TODAY()" Range("E9").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F9").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "" Range("F9").Select End Sub However, When I select C9 and click off it, nothing happens. Although E9 should have todays date value in it. Can you see where I am going wrong? I am using Excel 2003, and Column 3(C) is a dropdown list of values. Kind Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Cell Value within a macro | Excel Discussion (Misc queries) | |||
How to set variable to cell value in excel macro | Excel Discussion (Misc queries) | |||
Macro Creating Variable and using variable in a SQL statement | Excel Programming | |||
Border around Data Entered in Variable Rows | Excel Programming | |||
Writing variable from a Macro to a Cell | Excel Programming |