macro to move to another cell

  • Thread starter Thread starter Frappier
  • Start date Start date
F

Frappier

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.
 
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
 
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
 
That did it!!! Thank you both!

Don Guillett said:
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
(e-mail address removed)
Click to expand...
 
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.
 
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.
 
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
 
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
 
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?
 
Back
Top