| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
macro to move to another cell
I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
macro to move to another cell
You are almost there! Just need to insure that the activecell is Target
before calling test2: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then Target.Select test2 End If End If End Sub -- Gary''s Student - gsnu200812 "Frappier" wrote: I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
macro to move to another cell
That did it!!! Thank you both!
"Don Guillett" wrote: Try Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Frappier" wrote in message ... I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
macro to move to another cell
By saying the range is F1:F39 am I going to have a problem if rows are
entered? I would like this to apply to the new rows also. Thanks for your help. "Frappier" wrote: That did it!!! Thank you both! "Don Guillett" wrote: Try Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Frappier" wrote in message ... I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
macro to move to another cell
By saying the range is F1:F39 am I going to have a problem if rows are
added? I would like this to apply to the new rows also. "Don Guillett" wrote: Try Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Frappier" wrote in message ... I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
macro to move to another cell
Hi,
Just change the F1:F39 reference so its ready for as many cells as you want. or if all the cells down the the new row will have data you could modify the code to determine the row with the last data and increase the range by one. Private Sub Worksheet_Change(ByVal Target As Range) LastRow = Range("F1").End(XLDown).Row+1 If Not Application.Intersect(Range("F1:F"&LastRow), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Thanks, Shane Devenshire "Frappier" wrote: By saying the range is F1:F39 am I going to have a problem if rows are added? I would like this to apply to the new rows also. "Don Guillett" wrote: Try Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Frappier" wrote in message ... I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
macro to move to another cell
Or,
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("f")) Is Nothing Then Exit Sub If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "ShaneDevenshire" wrote in message ... Hi, Just change the F1:F39 reference so its ready for as many cells as you want. or if all the cells down the the new row will have data you could modify the code to determine the row with the last data and increase the range by one. Private Sub Worksheet_Change(ByVal Target As Range) LastRow = Range("F1").End(XLDown).Row+1 If Not Application.Intersect(Range("F1:F"&LastRow), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Thanks, Shane Devenshire "Frappier" wrote: By saying the range is F1:F39 am I going to have a problem if rows are added? I would like this to apply to the new rows also. "Don Guillett" wrote: Try Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Frappier" wrote in message ... I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
macro to move to another cell
Thanks to all of you for your rapid responses. My first piece of VBA works
like a charm. What a thrill!!!!!!! This could be addicting. If you could have one book on hand as a reference guide, which one would you pick? "Don Guillett" wrote: Or, Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Columns("f")) Is Nothing Then Exit Sub If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "ShaneDevenshire" wrote in message ... Hi, Just change the F1:F39 reference so its ready for as many cells as you want. or if all the cells down the the new row will have data you could modify the code to determine the row with the last data and increase the range by one. Private Sub Worksheet_Change(ByVal Target As Range) LastRow = Range("F1").End(XLDown).Row+1 If Not Application.Intersect(Range("F1:F"&LastRow), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Thanks, Shane Devenshire "Frappier" wrote: By saying the range is F1:F39 am I going to have a problem if rows are added? I would like this to apply to the new rows also. "Don Guillett" wrote: Try Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then target.offset(,-2)= InputBox("Enter Obligated Amount", "Input", 1) End If End If End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Frappier" wrote in message ... I am new to vba. I am trying to create a macro that once I have entered a date in any cell in column E, it will take me to column c (on the same row) to enter a dollar amount. So far I have created this macro Sub test2() NumberToBeInput = InputBox("Enter Obligated Amount", "Input", 1) ActiveCell.Offset(0, -2).Select ActiveCell.Value = NumberToBeInput End Sub and then on the sheet I have created the following Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("F1:F39"), Target) Is Nothing Then If IsDate(Target.Value) And Target.Value 0 Then test2 End If End If End Sub Please help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro to move cursor one cell right | New Users to Excel | |||
| macro - how to move to a specific cell and repeat | Excel Worksheet Functions | |||
| Macro to move one cell down | Excel Worksheet Functions | |||
| macro to move part of cell contents to another cell | Excel Discussion (Misc queries) | |||
| move to another cell within a subtotal report within a macro | Excel Worksheet Functions | |||
Linear Mode
