Excel VBA最后一列用于更新验证列表

Question

我在根据5中的内容中的最后一列调整数据验证列表时遇到麻烦。

这是我当前拥有的。

Sub DataRange_F() 'Foundation Drop Down List
Application.ScreenUpdating = False
Dim LastCol As Long
Dim Rng As Range
Dim WholeRng As Range
Dim ws As Worksheet
Dim wsR As Worksheet
Set ws = ThisWorkbook.Worksheets("Add New")
Set wsR = ThisWorkbook.Worksheets("Foundation Plates")
wsR.Activate
Set Rng = Cells
    LastCol = Rng.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    Set WholeRng = Range(Cells(5, "C"), Cells(5, LastCol))
    ws.Activate
    With ws.Range("E8").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:=WholeRng
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
Set ws = Nothing
Application.ScreenUpdating = True
End Sub

它一直在Formula1:=部分停止。这就是我被困的地方。如何在该公式中添加我的范围？还是有另一种方式？

谢谢

Answer 1

尝试一下...

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="=" & "'" & wsR.Name & "'!" & WholeRng.Address

Answer 2

尝试一下，

..., Formula1:=Chr(61) & WholeRng.Cells(1).Address(external:=true), Formula2:=Chr(61) & WholeRng.Cells(WholeRng.Cells.Count).Address(external:=true)

Answer 3

这就是我必须工作的。

Function GetColumnLetter(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter = vArr(0)
End Function
Sub DataRange()
Application.ScreenUpdating = False
Dim startCol As String
Dim startRow As Long
Dim lastCol As Long
Dim myCol As String
Dim rng As Range
Dim cell As Range
Dim sht2 As Worksheet
Set sht2 = ThisWorkbook.Worksheets("Foundation Plates")
Dim sht7 As Worksheet
Set sht7 = ThisWorkbook.Worksheets("Legend")
Call Unprotect
sht2.Activate
startCol = "C"
startRow = 5
lastCol = sht2.Cells(5, sht2.Columns.Count).End(xlToLeft).Column
myCol = GetColumnLetter(lastCol)
Set rng = sht2.Range(startCol & startRow & ":" & myCol & "5")
'For error checking the range
'MsgBox rng.Address
    sht7.Activate
    With sht7.Range("F8").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="=" & "'" & sht2.Name & "'!" & rng.Address
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
Call Protect
sht2.Activate
Set sht2 = Nothing
Set sht7 = Nothing
Set rng = Nothing
Application.ScreenUpdating = True
End Sub