Hi Mike.
I've used your code and it's giving me some problems. Hope you don't mind
me asking for your help.
When I run the 'auto date' code, it cancels out the vlookup code and creates
and error. After this happens, all the code in my workbook becomes
inoperable. Can you examine the code I'm using and give me some hints,
please?
---
Private Sub Worksheet_Change(ByVal Target As Range)
<<<<<<The following code runs the vlookup...>>>>>>
Dim rng As Range, res As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("B6:B5000")) Is Nothing Then Exit Sub
Set rng = Worksheets("StatePris").Range("A1:C50000")
res = Application.VLookup(Target, rng, 2, False)
Application.EnableEvents = False
If IsError(res) Then
Target.Offset(0, 1).Resize(1, 2).Value = ""
Else
Target.Offset(0, 1).Value = res
Target.Offset(0, 2).Value = Application.VLookup(Target, rng, 3, False)
Application.EnableEvents = True
End If
<<<<this code runs the auto date>>>>
With Target
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B6:B2000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
.NumberFormat = "dd mm yy"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
Thanks....
Mark
"Mike H" <(E-Mail Removed)> wrote in message
news:7566AEEE-E5E7-4E0F-A8F4-(E-Mail Removed)...
> Hi,
>
> You confused me a bit there because that was what it was supposed to do
> until I spotted a typo.
>
> Change this line
> Offset(0, 1).ClearContents
>
> to this
> Offset(0, -1).ClearContents
>
> Mike
>
>
>
>
> "nabanco" wrote:
>
>> Mike, is there a way to delete the date in the event there is nothing in
>> column B?
>>
>> "Mike H" wrote:
>>
>> > Hi,
>> >
>> > Right click the sheet tab, view code and paste this in.
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > With Target
>> > If .Count > 1 Then Exit Sub
>> > If Not Intersect(Range("B1:B16"), .Cells) Is Nothing Then
>> > Application.EnableEvents = False
>> > If IsEmpty(.Value) Then
>> > .Offset(0, 1).ClearContents
>> > Else
>> > With .Offset(0, -1)
>> > .NumberFormat = "dd mm yy"
>> > .Value = Now
>> > End With
>> > End If
>> > Application.EnableEvents = True
>> > End If
>> > End With
>> > End Sub
>> >
>> > Mike
>> >
>> > "nabanco" wrote:
>> >
>> > > Hello,
>> > >
>> > > How would I alter the below to put today's date in column A if a word
>> > > is
>> > > entered into column B? For example, when "tower" is typed into B16,
>> > > A16
>> > > automatically puts todays date in 07/07/07 format?
>> > >
>> > > Thanks for your help
|
Similar Threads
