|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- sub testD()
- Dim d as object, arr, brr
- arr = Sheets("价目表").[a1].CurrentRegion<div>
- 'brr = [a1].CurrentRegion
- <div>'如果数据区域包括空行,可使用如下“万金油”语句</div><div>Dim lastRow%, lastCol%</div><div>with ActiveSheet</div><div><div><font color="#ff0000"> lastRow = .Cells.Find("*", [A1], xlFormulas, xlPart, xlByRows, xlPrevious, False).Row</font></div><div><font color="#ff0000"> lastCol = .Cells.Find("*", [A1], xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column</font></div></div><div> brr = .Range(.[A1], .Cells(lastRow, lastCol))</div><div>end with</div><div>
- </div><div>'红色的语句属于对字典对象的常用操作,建议去学习VBA字典的用法</div><div>Set d = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr, 1)
- <font color="#ff0000">d(arr(i, 1)) = arr(i, 3)</font>
- Next
- For i = 2 To UBound(brr, 1)
- <font color="#ff0000">If d.exists(brr(i, 1)) Then brr(i, 3) = d.Item(brr(i, 1))</font>
- Next
- <font color="#ff0000"> [a1].Resize(UBound(brr, 1), UBound(brr, 2)) = brr</font>
- End Sub</div></div>
复制代码
|
|