我正在处理一个包含两个下拉列表的现有电子表格。是否可以导出或编辑这些下拉列表中的数据?
我正在处理一个包含两个下拉列表的现有电子表格。是否可以导出或编辑这些下拉列表中的数据?
Sub Sample()
Dim ws As Worksheet
Dim dvRng As Range, rng As Range
Dim strList As String
Dim MyAr() As String
Set ws = ThisWorkbook.Sheets("Sheet1")
Set dvRng = ws.Range("A1") ' Case A
'Set dvRng = ws.Range("C1") ' Case B
'Set dvRng = ws.Range("E1") ' Case C
'~~> Get the formula in the data validation
strList = dvRng.Validation.Formula1
'~~> Check if it has an = sign for Case A and Case C
If InStr(1, strList, "=") Then
strList = Replace(strList, "=", "")
Set rng = Range(strList)
rng.Copy Sheet2.Range("A1")
Else
'~~> Case B
If InStr(1, strList, ",") Then
MyAr = Split(strList, ",")
Sheet2.Range("A1:A" & UBound(MyAr) + 1).Resize.Value = Application.Transpose(MyAr)
Else
Sheet2.Range("A1").Value = strList
End If
End If
End Sub
我已经对代码进行了注释,所以你不应该遇到任何问题。如果还有问题,请直接询问:)
除非有更好的想法,否则我已经将每个项目选择到不同的单元格中,然后将其复制到一个 CSV 文件中,以便可以导入到表中。