Hello.

I've used a recent code from this group 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 my 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

I don't understand your problem. I have tried with and without a match and
it ran through fine for me.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bruise" wrote in message
...
Hello.

I've used a recent code from this group 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 my 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

My problem is what I explained. I'm glad it worked for you, but I'm using a
rather extensive workbook with a ton of code on about 35 worksheets. This
is the only code used on this particular worksheet. However, when I use it
with the autodate operable (I have it blanked out right now), it shows an
error.

Wait... this is really strange. I just reactivated the code for the
autodate and tried the program and it worked great. I even deleted some
text and retried it and still worked.

Now, I'm somewhat familiar with Excel and VBA, but it still makes my head
spin sometimes.

Whatever you did, Bob, thanks! :) Disregard my request for assistance.
I'll use my Get Out Of Jail card another time.

Mark

"Bob Phillips" wrote in message
...
I don't understand your problem. I have tried with and without a match and
it ran through fine for me.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bruise" wrote in message
...
Hello.

I've used a recent code from this group 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 my 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

OK. Disregard that last message. It's doing it again. I can't use the
code without it creating an error and disabling the entire code in my
project. I've done nothing different, but close out of the program and
reopen it.

It's frustrating. Help???


"Bruise" wrote in message
...
My problem is what I explained. I'm glad it worked for you, but I'm using
a rather extensive workbook with a ton of code on about 35 worksheets.
This is the only code used on this particular worksheet. However, when I
use it with the autodate operable (I have it blanked out right now), it
shows an error.

Wait... this is really strange. I just reactivated the code for the
autodate and tried the program and it worked great. I even deleted some
text and retried it and still worked.

Now, I'm somewhat familiar with Excel and VBA, but it still makes my head
spin sometimes.

Whatever you did, Bob, thanks! :) Disregard my request for assistance.
I'll use my Get Out Of Jail card another time.

Mark

"Bob Phillips" wrote in message
...
I don't understand your problem. I have tried with and without a match and
it ran through fine for me.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Bruise" wrote in message
...
Hello.

I've used a recent code from this group 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 my 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