|
各位老师有一个小白的问题 请教:
下面的程序是引用当前工作簿目录下其它工作簿数据的代码,我如果想改成引用同一工作簿中“Sheets("基础数据")”工作表的数据该怎样写代码?
代码如下:- Private Sub CommandButton1_Click()
- Dim Arr, myPath$, myName$, Arr1, Myr&, d, Myr1&, m&
- Application.ScreenUpdating = False
- Set d = CreateObject("Scripting.Dictionary")
- Sheets("分项报价").Activate
- Myr = Cells(Rows.Count, 4).End(xlUp).Row
- Range("e4:m" & Myr).ClearContents
- Arr = Range("a4:m" & Myr)
- myPath = ThisWorkbook.Path & ""
- myName = Dir(myPath & "data.xls")
- With GetObject(myPath & myName)
- With .Sheets("data")
- Myr1 = .Cells(.Rows.Count, 4).End(xlUp).Row
- Arr1 = .Range("a1:J" & Myr1) '
- For i = 1 To UBound(Arr1)
- d(Arr1(i, 4)) = i
- Next
- End With
-
- End With
- For i = 1 To Myr - 3
- If d.Exists(Arr(i, 4)) Then
- m = d(Arr(i, 4))
- Arr(i, 2) = Arr1(m, 2)
- Arr(i, 3) = Arr1(m, 3)
- Arr(i, 5) = Arr1(m, 5)
- Arr(i, 10) = Arr1(m, 7)
- Arr(i, 9) = Arr1(m, 10)
- End If
- Next
- Range("a4:m" & Myr) = Arr
- For i = 1 To UBound(Arr)
- If Arr(i, 2) <> "" Then Cells(i + 3, 8) = "=rc[-1]*rc[-2]"
- If Arr(i, 2) <> "" Then Cells(i + 3, 7) = "=rc[3]*rc[5]"
- If Arr(i, 2) <> "" Then Cells(i + 3, 11) = "=rc[-1]*rc[-5]"
- Next
- Application.ScreenUpdating = True
- End Sub
复制代码
- Private Sub CommandButton1_Click()
- Dim Arr, myPath$, myName$, Arr1, Myr&, d, Myr1&, m&
- Application.ScreenUpdating = False
- Set d = CreateObject("Scripting.Dictionary")
- Sheets("分项报价").Activate
- Myr = Cells(Rows.Count, 4).End(xlUp).Row
- Range("e4:m" & Myr).ClearContents
- Arr = Range("a4:m" & Myr)
- With Sheets("基础数据")
- Myr1 = .Cells(.Rows.Count, 4).End(xlUp).Row
- Arr1 = .Range("a1:J" & Myr1) '
- For i = 1 To UBound(Arr1)
- d(Arr1(i, 4)) = i
- Next
- End With
-
- For i = 1 To Myr - 3
- If d.Exists(Arr(i, 4)) Then
- m = d(Arr(i, 4))
- Arr(i, 2) = Arr1(m, 2)
- Arr(i, 3) = Arr1(m, 3)
- Arr(i, 5) = Arr1(m, 5)
- Arr(i, 10) = Arr1(m, 7)
- Arr(i, 9) = Arr1(m, 10)
- End If
- Next
- Range("a4:m" & Myr) = Arr
- For i = 1 To UBound(Arr)
- If Arr(i, 2) <> "" Then Cells(i + 3, 8) = "=rc[-1]*rc[-2]"
- If Arr(i, 2) <> "" Then Cells(i + 3, 7) = "=rc[3]*rc[5]"
- If Arr(i, 2) <> "" Then Cells(i + 3, 11) = "=rc[-1]*rc[-5]"
- Next
- Application.ScreenUpdating = True
- End Sub
复制代码刚才没注意,with 后面的sheets前多了个点。哈哈
|
|