|
目的:做一个进销存的处理表格
过程:对前面三张表的款号进行合并去重,然后分别对前三张表的款号进行数量和金额的求和计算
问题:将数组写入表格时,出现下标越界;进行调试时目标数组的行列上下标都没发现问题
请大神指导
代码如下:
Sub 区域选择()
Dim i&
Sheets("进销存情况表").UsedRange.Offset(1).Clear
For i = 1 To 3
Intersect(Sheets(i).UsedRange.Offset(1, 0), Sheets(i).Range("B:B")).Copy _
Sheets("进销存情况表").Cells(Rows.Count, 1).End(xlUp).Offset(1).Cells(1)
Sheets("进销存情况表").Range("A2:A" & Sheets("进销存情况表").Cells(Rows.Count, 1).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
Next
Sheets("进销存情况表").Range("A2:A" & Sheets("进销存情况表").Cells(Rows.Count, 1).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
Call 进销存处理
End Sub
Sub 进销存处理()
Dim i&, j&, k&, m&, Lookup_value, Table_arry, Table_brry, table_crry, Uwant()
Lookup_value = Sheets("进销存情况表").Range("A2:A" & Sheets("进销存情况表").Cells(Rows.Count, 1).End(xlUp).Row)
Table_arry = Sheets("期初库存").Range("B2:E" & Sheets("期初库存").Cells(Rows.Count, 2).End(xlUp).Row)
Table_brry = Sheets("本期入库").Range("B2:E" & Sheets("本期入库").Cells(Rows.Count, 2).End(xlUp).Row)
table_crry = Sheets("本期出库").Range("B2:E" & Sheets("本期出库").Cells(Rows.Count, 2).End(xlUp).Row)
ReDim Uwant(1 To UBound(Lookup_value), 1 To 8)
For i = 1 To UBound(Lookup_value)
For j = 1 To UBound(Table_arry)
If Lookup_value(i, 1) = Table_arry(j, 1) Then
Uwant(i, 1) = Uwant(i, 1) + Table_arry(j, 2)
Uwant(i, 5) = Uwant(i, 5) + Table_arry(j, 4)
End If
Next j
Next i
Sheets("进销存情况表").Range("b2").Resize(UBound(Lookup_value, 8)) = Uwant ’ 调试时这一步下标越界 :(
End Sub
|
|