|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
'论坛上搜的
Sub 标签76_单击()
Dim RCount As Long, CCount As Integer
Dim Temp$, Temp1$, Temp2$, Temp3$, Temp4$, temp5$, val$, Index&, Num%
'获取源工作薄的路径
If Dir(ThisWorkbook.Path & "\资料.xls") <> "" Then
Temp = "'" & ThisWorkbook.Path & "\[资料.xls]酒店'!"
Else
Temp = "'" & Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 11) & "\[资料.xls]酒店'!"
End If
'获取源工作薄指定工作表的列数
Temp1 = Temp & Rows(1).Address(, , xlR1C1)
Temp1 = "Counta(" & Temp1 & ")"
CCount = Application.ExecuteExcel4Macro(Temp1)
'获取源工作薄指定工作表的行数合计
Temp2 = Temp & Columns("A").Address(, , xlR1C1)
Temp2 = "Counta(" & Temp2 & ")"
Temp2 = "Counta('Z:\[资料.xls]陪同'!C1)"
RCount = Application.ExecuteExcel4Macro(Temp2)
val = ActiveWorkbook.Sheets("确认").Range("D21").Value
For Index = 1 To RCount
Temp1 = Temp & Cells(Index, 1).Address(, , xlR1C1)
If Application.ExecuteExcel4Macro(Temp1) = val Then
Temp2 = Temp & Cells(Index, 2).Address(, , xlR1C1)
Temp3 = Temp & Cells(Index, 3).Address(, , xlR1C1)
Temp4 = Temp & Cells(Index, 4).Address(, , xlR1C1)
ActiveWorkbook.Sheets("确认").Range("D22").Value = Application.ExecuteExcel4Macro(Temp2) '写入电话
ActiveWorkbook.Sheets("确认").Range("D23").Value = Application.ExecuteExcel4Macro(Temp3) '写入FAX
ActiveWorkbook.Sheets("确认").Range("J21").Value = Application.ExecuteExcel4Macro(Temp4) '写入联系人
Exit For
End If
Next Index
End Sub |
|