snidera
09-15-2006, 12:49 PM
I have a Part Number colume that i want to use as a selectable list, but it will change based on the model number.
For j =1 to ubound(models())
If Cells(i, 6) = models(j) Then
With Cells(i, 7).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:='ModelName(j)'
.InCellDropdown = True
.IgnoreBlank = True
End With
End If
Next j
-I have dynamic named ranges for each list of part numbers and the names are in another array of the same length- ModelName() (String format ModelName_Model#)
-Currently both Model() and ModelName() are sized 1 to 4 if that helps simulate
-The user will enter the partnumbers with a name and model# for the heading the array models() is created from that).
-The named range for each part number is ModelName_model#
Is there any way to insert the named range into Formula1 from my code above? I'm currently using If/ElseIf statements to do this, but if a user inputs more/different models, i have to modify the code
For j =1 to ubound(models())
If Cells(i, 6) = models(j) Then
With Cells(i, 7).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:='ModelName(j)'
.InCellDropdown = True
.IgnoreBlank = True
End With
End If
Next j
-I have dynamic named ranges for each list of part numbers and the names are in another array of the same length- ModelName() (String format ModelName_Model#)
-Currently both Model() and ModelName() are sized 1 to 4 if that helps simulate
-The user will enter the partnumbers with a name and model# for the heading the array models() is created from that).
-The named range for each part number is ModelName_model#
Is there any way to insert the named range into Formula1 from my code above? I'm currently using If/ElseIf statements to do this, but if a user inputs more/different models, i have to modify the code