Cool. Yeah, my VBA is still pretty bad, and I am learning. I get it to do
what I need done, but beyond that.... :) Happy days!
--
John C


"SJAdams4444" wrote:

I should thank you John C, not only have you helped me with this code, but
you have also got me learning, and I greatly appreciate that.
I actually was able to figure out that problem. If you add in
If isect = 1 Then
******* If Target.Count 1 Then Exit Sub
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0
Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If

The code will work when you are working with more than one cell at a time in
the row.


"SJAdams4444" wrote:

Well John C I am very much hoping you will return to answer another question.

The last code you gave me works beautifully, but it still runs into one
error...
I can clear contents of the cells on at a time without an error, but if i
try to select more than one cell and clear the contents, it runs into an
error on the
If Target = "" Then NullCheck = "Y"
line, saying that there is a type mismatch.
Is there another way to set the nullcheck that can account for the "multiple
nulls" that arise from doing a ClearContents on multiple cells?


"John C" wrote:

Starting new portion of thread, as the other keeps scooting to the right.....
I got rid of the Look1 variable, and added a checker to ensure that it fits
into your range. I currently have this code set up for $A$2:$A$1000, if you
are going to be going beyond 1000 rows on your Main tab, then you will need
to increase that number.

My new VBA code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Look2 As Variant
Dim Look3 As Range
Dim NullCheck As String
Dim TblLkUp As Range
Dim isect As Integer
isect = 1
Set TblLkUp = Worksheets("Data").Range(Worksheets("Data").Range( "$E$1"))
Set Look3 = Worksheets("Data").Range(Worksheets("Data").Range( "$F$1"))
NullCheck = "N"
If Intersect(Target, Range("$A$2:$A$10000")) Is Nothing Then
isect = 0
End If
If isect = 1 Then
If Target = "" Then NullCheck = "Y"
If NullCheck = "N" Then
If Application.WorksheetFunction.CountIf(Look3, Target) = 0 Then
Look2 = Application.WorksheetFunction.VLookup(Target,
TblLkUp, 2, False)
Target = Look2
End If
End If
End If
End Sub



--
John C


"SJAdams4444" wrote:

I want to be able to create a drop down list in ONE cell that shows names of
certain accounts, but when you select the name of the account, the number
corresponding to that account shows in the SAME cell. Is this at all
possible?