关于数据分配的问题

brianlong · 发表于 2024-9-15 22:12

如附件订单中每个型号的总数跟记录中每个型号的总数是对应的，最小包装是10000，因为装箱和批次比较散，现需要将每个型号的订单号依次对应到每一行去，保证每个型号的总数跟记录中每个型号的总数不会错，第一次处理这种问题，请求各位帮忙

jaxxcyh · 发表于 2024-9-16 00:24

本帖最后由 jaxxcyh 于 2024-9-16 08:36 编辑

Sub test()
Sheets("记录").Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row).Value = ""
Dim arr_dingdan(), arr_jilu()
arr_dingdan = Sheets("订单信息").[a1].CurrentRegion.Value
arr_jilu = Sheets("记录").[a1].CurrentRegion.Value
For i = 2 To UBound(arr_dingdan, 1) '逐个处理每笔订单
j = arr_dingdan(i, 3) / 10000 '将订单拆开为若干个10000
Do
'"记录"表中寻找匹配的记录行
k = 2
Do While arr_jilu(k, 5) <> "" Or arr_jilu(k, 2) <> arr_dingdan(i, 2)
k = k + 1
Loop
If arr_jilu(k, 2) = arr_dingdan(i, 2) Then '如果型号一致
arr_jilu(k, 5) = arr_dingdan(i, 1) '就在记录里写入订单号
j = j - 1
End If
Loop Until j = 0
Next
'输出结果
For i = 2 To UBound(arr_jilu, 1)
Sheets("记录").Cells(i, 5).Value = arr_jilu(i, 5)
Next
End Sub

复制代码

jaxxcyh · 发表于 2024-9-16 00:34

附件..............

Sub test()
Sheets("记录").Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row).Value = ""
Dim arr_dingdan(), arr_jilu()
arr_dingdan = Sheets("订单信息").[a1].CurrentRegion.Value
arr_jilu = Sheets("记录").[a1].CurrentRegion.Value
For i = 2 To UBound(arr_dingdan, 1) '逐个处理每笔订单
j = arr_dingdan(i, 3) / 10000 '将订单拆开为若干个10000
Do
'"记录"表中寻找匹配的记录行
k = 2
Do While arr_jilu(k, 5) <> "" Or arr_jilu(k, 2) <> arr_dingdan(i, 2)
k = k + 1
Loop
If arr_jilu(k, 2) = arr_dingdan(i, 2) Then '如果型号一致
arr_jilu(k, 5) = arr_dingdan(i, 1) '就在记录里写入订单号
j = j - 1
End If
Loop Until j = 0
Next
'输出结果
For i = 2 To UBound(arr_jilu, 1)
Sheets("记录").Cells(i, 5).Value = arr_jilu(i, 5)
Next
End Sub

复制代码

gwjkkkkk · 发表于 2024-9-16 14:27

Option Explicit
Sub TEST1()
Dim ar, br, i&, j&

ar = Worksheets(2).[A1].CurrentRegion.Value

With [A1].CurrentRegion
      With .Columns(.Columns.Count)
         Intersect(.Offset(), .Offset(1)).Clear
      End With
      br = .Value
      For i = 2 To UBound(ar)
         For j = 2 To UBound(br)
            If Len(br(j, 5)) = False Then
                  If ar(i, 3) = 0 Then Exit For
                  If br(j, 2) = ar(i, 2) Then
                     br(j, 5) = ar(i, 1)
                     ar(i, 3) = ar(i, 3) - br(j, 4)
                  End If
            End If
         Next j
      Next i
      .Value = br
End With

Beep
End Sub

gwjkkkkk · 发表于 2024-9-16 14:27

参与一下。。。

		自动登录	找回密码
密码			免费注册

[求助] 关于数据分配的问题

评分

评分

评分

评分