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) .Validation

.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

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
.

on 9-7-2004, Sworkhard supposed :

With Worksheets("Customers & Jobs")
If Cells(7, ActiveCell.Column).Value = "Extra" Then

Cells on the last line is referring the the active sheet NOT (as you
probably intended)
to the cells of worksheet C&J because the . is missing before the
..Cells on the 2nd line

Worksheets("y").activate

With Worksheets("x")
'with a dot it will always refer to ws x
..Cells(1,1).value =3
'without the dot is will refer to activesheet in this case y
Cells(1,1).value = 3
end with

HTH,
keepITccol

--
This is an automatic signature of MesNews.
Site : http://mesnews.no-ip.com

actually, it is supposed to refer to the active sheet as whether or not the code proceeds depends on whether or not that cell has "Contract" or "Extra" or nothing at all in it. The With Worksheets("Customers & Jobs") is used later in the code when it has to find a location and offset it by two columns and get the value from that and then insert it back into the activesheet after looping to find all the places where a certain customer's name is and combining all the different job descriptions to form a list.


"keepITcool" wrote:

on 9-7-2004, Sworkhard supposed :

With Worksheets("Customers & Jobs")
If Cells(7, ActiveCell.Column).Value = "Extra" Then

Cells on the last line is referring the the active sheet NOT (as you
probably intended)
to the cells of worksheet C&J because the . is missing before the
..Cells on the 2nd line

Worksheets("y").activate

With Worksheets("x")
'with a dot it will always refer to ws x
..Cells(1,1).value =3
'without the dot is will refer to activesheet in this case y
Cells(1,1).value = 3
end with

HTH,
keepITccol

--
This is an automatic signature of MesNews.
Site : http://mesnews.no-ip.com