|
我编写了一下程序,但不知道为什么,K不能循环,求版主帮忙!!十分感谢!!!
Sub 搜索资料()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim c As Boolean
Dim ic1 As Integer
Dim ic2 As Integer
ic1 = Sheets("销售信息").[a4].CurrentRegion.Rows.Count
ic2 = Sheets("货品信息").[a3].CurrentRegion.Rows.Count
c = False
For k = 1 To ic1
For i = 1 To ic2
If Trim(Sheets("销售信息").Cells(k, 1)) = Trim(Sheets("货品信息").Cells(i, 1)) Then
For j = 1 To 13
Sheets("销售信息").Cells(k, j) = Sheets("货品信息").Cells(i, j)
c = True
Next j
Exit For
End If
Next i
Next k
MsgBox "已经搜索到符合条件的记录", vbOKOnly, "提示"
If c = False Then
MsgBox "没有搜索到符合条件的记录,请核实", vbOKOnly, "提示"
End If
End Sub
本帖最后由 happym8888 于 2012-8-18 16:46 编辑
ic1 = Sheets("销售信息").[a4].CurrentRegion.Rows.Count '这个写法不合适,CurrentRegion.Rows.Count表示当前区域的行数,里面没有定位信息。应该写成:
ic1= Sheets("销售信息").range("A65536").End(xlup).row
同理:
ic2 = Sheets("货品信息").range("A65536").end(xlup).row - Sub 搜索资料()
- Dim i As Integer
- Dim j As Integer
- Dim k As Integer
- Dim c As Boolean
- Dim ic1 As Integer
- Dim ic2 As Integer
- ic1 = Sheets("销售信息").Range("A65536").End(xlUp).Row '[a4].CurrentRegion.Rows.Count '这个写法不合适,CurrentRegion.Rows.Count表示当前区域的行数,里面没有定位信息。
- ic2 = Sheets("货品信息").Range("A65536").End(xlUp).Row '[a3].CurrentRegion.Rows.Count
- c = False
- For k = 4 To ic1 'k直接从第4行开始循环
- For i = 3 To ic2 'i直接从第3行开始循环
- xxx = Trim(Sheets("销售信息").Cells(k, 1))
- yyy = Trim(Sheets("货品信息").Cells(i, 1))
- If Trim(Sheets("销售信息").Cells(k, 1)) = Trim(Sheets("货品信息").Cells(i, 1)) Then
- For j = 1 To 13
- Sheets("销售信息").Cells(k, j) = Sheets("货品信息").Cells(i, j)
- c = True
- Next j
- Exit For
- End If
- Next i
- Next k
- If c = True Then
- MsgBox "已经搜索到符合条件的记录", vbOKOnly, "提示" '本来的写法不好、没有搜索到符合条件的记录也会提示“已经搜索到符合条件的记录”
- Else
- MsgBox "没有搜索到符合条件的记录,请核实", vbOKOnly, "提示"
- End If
- End Sub
- Sub 货品出仓()
- Dim i As Integer
- Dim j As Integer
- Dim k As Integer
- Dim cz As Boolean
- Dim ic1 As Integer
- Dim ic2 As Integer
- ' ic1 = Sheets("货品信息").[a3].CurrentRegion.Rows.Count
- ' ic2 = Sheets("销售信息").[a4].CurrentRegion.Rows.Count
- ic1 = Sheets("销售信息").Range("A65536").End(xlUp).Row
- ic2 = Sheets("货品信息").Range("A65536").End(xlUp).Row
-
- cz = False
- For k = 4 To ic2
- For i = 3 To ic1
- If Trim(Sheets("销售信息").Cells(k, 1)) = Trim(Sheets("货品信息").Cells(i, 1)) And Trim(Sheets("销售信息").Cells(i, 12)) > 0 Then
- cz = True
- Sheets("货品信息").Cells(i, 12) = Sheets("货品信息").Cells(i, 12) - Sheets("销售信息").Cells(k, 14)
- For j = 1 To 13
- Sheets("销售信息").Cells(k, j) = Sheets("货品信息").Cells(i, j)
- Next j
- Exit For
- End If
- Next i
- Next k
- If cz = True Then
- MsgBox "货品已经导出成功!", vbOKOnly, "成功导出"
- Else
- MsgBox "记录表无此记录或库存量为0,因此无法导出,请核实!", vbOKOnly, "无法导出"
- End If
- End Sub
复制代码
表格.rar
(11.71 KB, 下载次数: 6)
|
|