Hi,
i think you have to many variants. Some variants do not
work well with ranges or cells.
Use the Set command to set a range or cell value.
set a= activecell.offset(0,7)

if you want to send me dummy sheet(s) of the project, I
can write a userform of the task. This way, you can have
many selections of various types.
See some of my handiwork:
http://au.geocities.com/excelmarksway
http://www.geocities.com/excelmarksway


I will need a real email address to send the results to.
regards
Mark


Confidentiality assured.
-----Original Message-----
Dim vrtSheetName As Variant
Dim vrtJobValue, vrtCustomerLocation As Variant
Dim vrtCustomerName, vrtValidationValue As Variant
Dim vrtOriginalLocation, vrtFirstAddress As Variant
Dim vrtValidationValueOld As Variant
Dim Msg, ans As Variant
Dim vrtCustomerLocation2 As Variant

vrtSheetName = ActiveSheet.Name

If ActiveCell.Row = 6 Then

With Worksheets("Customers & Jobs")

If Cells(7, ActiveCell.Column).Value
= "Extra" Then

vrtCustomerName = Worksheets("Customers &
Jobs").Cells(7, ActiveCell.Column - 1).Value

Worksheets("Customers & Jobs").Visible =
True

Set vrtCustomerLocation = Range
("Customers").Find(What:=vrtCustomerName, LookIn:=xlValues)
vrtJobValue = Cells(Range
(vrtCustomerLocation).Row, 5).Value
vrtValidationValue = vrtJobValue
vrtOriginalLocation =
vrtCustomerLocation

MsgBox Prompt:=vrtCustomerLocation

If Not vrtCustomerLocation = Empty
Then

vrtFirstAddress =
vrtOriginalLocation

Do

vrtCustomerLocation
= .FindNext(vrtFirstAddress)
vrtJobValue = .Cells(Range
(vrtCustomerLocation).Row, 5).Value
vrtValidationValueOld =
vrtValidationValue
vrtValidationValue =
vrtValidationValueOld & "," & vrtJobValue

Loop While vrtCustomerLocation <
vrtFirstAddress And Not vrtJobValue = Empty

With Worksheets
(vrtSheetName).Range(ActiveCell.Address).Validatio n

.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:=vrtValidationValue

End With
End If

'Worksheets("Customers & Jobs").Visible =
False
End If
End With
End If

In the code above I'm trying to lookup a address, store
it in a variable, and then take data off of an inactive
sheet and insert it into a dropdown list using data
validation.

Any help is appreciated
.