Using VBA to select a new drop down list for each new drop down

[avernamethyst112]

Hey guys,

I'm running into the issue where I would like to create a drop down in each cell in the range h4:h74, and for each drop down, have each drop down reference a new data set from B86:BT86. So for cell H4, the drop down in that cell should reference B86:some ending point, H5 should reference C86:some reference point, etc.

Below is the code I've written so far,

Sub list()
Dim rangecount As Integer
Dim wks As Worksheet
Dim lstRng As range

Set wks = ThisWorkbook.Worksheets("Cash Adjustment")
rangecount = range(range("B4"), range("B4").End(xlDown)).Count
Sheets("Cash Adjustment").Select
i = 4
For k = 2 To rangecount + 1
Set lstRng = wks.range(Cells(86, k), Cells(65536, k).End(xlUp))
ActiveWorkbook.Names.Add Name:="list", RefersToR1C1:=lstRng
With wks.Cells(i, 8).Validation
.delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=list"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
i = i + 1
Next k

End Sub

However, this is just filling all of the dropdowns with the range that's dictated by the last k value. Any thoughts on what's going wrong here? I've been banging my head against this for a day now.

Thank you so much!

 

[Fluff]

Hi & welcome to MrExcel.
Try

ActiveWorkbook.Names.Add Name:=[COLOR=#ff0000]"list" & K[/COLOR], RefersToR1C1:=lstRng
With wks.Cells(i, 8).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="[COLOR=#ff0000]=list" & K[/COLOR]

 

[avernamethyst112]

Thank!! You are brilliant. This worked perfectly.

 

[Fluff]

You're welcome & thanks for the feedback