|
请各位高手帮我看看
在网上抄了两段代码,分开都可以用。合并报错
Private Sub Worksheet_SelectionChange(ByVal Target As Range) '在用户更改工作表中的单元格选择时触发。
Set dict = CreateObject("Scripting.Dictionary")
' Dim cell As Range
Dim 最后一行 As Long
Dim i As Long
Dim theList As String
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub '如果用户最近更改的单元格(Target)不是Range("B3")单元格,则退出当前子程序。
With Sheets("数据")
最后一行 = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To 最后一行
If Not dict.Exists(.Range("B" & i).Value) Then
dict.Add .Range("B" & i).Value, .Range("C" & i).Value
End If
Next i
End With
' 将字典的键转换为用逗号隔开的字符串格式
Dim arr As Variant: arr = dict.Keys
theList = Join(arr, ",")
If Target.Address = "$B$3:$D$3" Then
If theList = "" Then
' 如果可选项为空,则删除现有验证并允许手输入
Target.Validation.Delete
Target.Validation.Add Type:=xlValidateText, AlertStyle:=xlValidAlertStop
Else
' 如果有可选项,则将字典所有的键作为下拉列表的选项
Target.Validation.Delete
Target.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, _
Formula1:=theList
'根据输入值自动填充数据
With ThisWorkbook.Worksheets("付款申请单")
Dim row1 As Long
row1 = Target.Row
If Target <> "" Then
Dim T
For T = 1 To ThisWorkbook.Worksheets("数据").Cells(1000000, 2).End(xlUp).Row
If Target.Value = ThisWorkbook.Worksheets("数据").Cells(T, 2) Then
.Cells(row1, 6) = ThisWorkbook.Worksheets("数据").Cells(T, 4) '开户银行
.Cells(row1 + 1, 2) = ThisWorkbook.Worksheets("数据").Cells(T, 3) '收款账号
Exit Sub
End If
Next T
MsgBox "未找到对应商品"
Target = ""
.Cells(row1, 4) = ""
.Cells(row1 + 1, 2) = ""
Else
.Cells(row1, 4) = ""
.Cells(row1 + 1, 2) = ""
End If
End With
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'付款金额人民币大写
If Target.Address = "$H$7" Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Offset(, -6) = DX(Target.Value)
Application.EnableEvents = True
End If
End If
End Sub
|
|