Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim wb As Workbook
Dim i As Byte
Dim j As String
On Error Resume Next
If Sh.Name = "Sheet1" And Target.Address = "$E$5" Then
Set wb = Workbooks.Open(ThisWorkbook.Path & "\价格表.xls")
With wb
For i = 2 To 7
If .Sheets("sheet1").Cells(i, 1) = Target Then
j = .Sheets("sheet1").Cells(i, 2)
Exit For
End If
j = "查不到"
Next i
End With
wb.Close
Application.EnableEvents = False
Sh.Range("e7").Value = j
Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SH As Worksheet, 价格 As Workbook, PRICE As Variant, I As Integer, T As Integer
T = 0
Set SH = ThisWorkbook.ActiveSheet
If SH.Name = "Sheet1" And Target.Address = "$E$5" Then
Set 价格 = Workbooks.Open("C:\Documents and Settings\1010018\桌面\第8课作业题查找\价格表.xls")
For I = 1 To 10
If UCase(Target.Value) = UCase(ActiveSheet.Cells(I, 1)) Then '加UCASE就可以不区分大小写
PRICE = ActiveSheet.Cells(I, 2)
T = 1
价格.Close False
Exit For
End If
Next
If T <> 1 Then
价格.Close False
MsgBox "查找不到"
Else
SH.Range("E7") = PRICE
PRICE = 0
T = 0
End If
End If
Set SH = Nothing
End Sub
忘记写ID了
22楼提交答案者信息如下：
C09：sliang28
E05:linch92413交作业
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim MyBok As Workbook
Dim i As Long
Dim Pro As String
Dim D As Long
Pro = Range("e5")
If Sh.Name = "Sheet1" And Target.Address = "$E$5" Then
For i = 1 To Workbooks.Count
If Workbooks(1).Name = "价格表.xls" Then GoTo 100
Next i
Set MyBok = Workbooks.Open(ThisWorkbook.Path & "\价格表.xls")
100:
With MyBok.Sheets(1)
For i = 2 To .Range("a65536").End(xlUp).Row
If .Range("a" & i) = Pro Then
D = Range("a" & i).Offset(0, 1)
Exit For
End If
Next i
End With
Workbooks("价格表.xls").Close False
If D = 0 Then
Range("e7") = "查找不到"
Exit Sub
End If
Range("e7") = D
End If
End Sub
总算搞定了，恳请校长下周公布标准代码，也好观摩学习。
开始看着作业挺难的，无从下手的感觉，看了5遍视频，翻了N遍书，懂点写点，等写完原来如此……
A06：梅一枝 作业：
Option Explicit
Option Compare Text
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sh1
Dim rg As Range
Dim x As Integer
If Sh.Name = "Sheet1" And Target.Address = "$E$5" Then
Workbooks.Open ThisWorkbook.Path & "/价格表.xls"
Set sh1 = Workbooks("价格表.xls").Sheets("sheet1")
For x = 2 To 7
If Sh.Range("$E$5") = sh1.Range("a" & x) Then
Sh.Range("$E$7") = sh1.Range("B" & x)
Exit For
End If
Next x
If x = 8 Then
Sh.Range("$E$7") = "查找不到"
End If
End If
End Sub
老师，作业中
Dim sh1
Set sh1 = Workbooks("价格表.xls").Sheets("sheet1")
变量SH1明明代表的是sheet 工作表，用DIMSH1 AS SHEETS 不对吗？老提示：编译错误，方法和数据成员未找到。。。。。声明类型为 sheet，workbookS,WORKBOOK,RENGE,等等我都试验过，肯定都错误……只好 不声明类型了。为什么呢？这是为什么呢？纠结~
对了，我作业中E5输入的字母可以不区分大小写，也运行成功了，我可以很自豪的大声喊：梅一枝V入门成功！！
:'$老师您辛苦了，多给些积分吧……):P
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 5 And Target.Row = 5 Then
Workbooks.Open ThisWorkbook.Path & "/价格表.xls"
Dim x As Integer
For x = 2 To 7
If Target.Value = Cells(x, 1) Then
Target.Offset(2, 0).Value = Cells(x, 2)
Else
Target.Offset(2, 0).Value = "查找不到"
End If
Next x
End If
End Sub
A07:一缕忧兰
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim wb As Workbook
Dim a As Integer
Dim str1 As String
Dim str2 As String
Application.ScreenUpdating = False
str1 = ThisWorkbook.Worksheets("Sheet1").Range("E5").Value
If Sh.Name = "Sheet1" And Target.Address = "$E$5" Then
Set wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "\价格表.xls")
wb.Activate
For a = 2 To 7
If Cells(a, 1) = str1 Then
str2 = Cells(a, 2)
End If
Next a
If str2 = "" Then
str2 = "查不到"
End If
wb.Close SaveChanges:=False
ThisWorkbook.Sheets("Sheet1").Range("E7") = str2
End If
Application.ScreenUpdating = True
End Sub