VBA自动生成询证函代码求解答

lmr1107

刚看完《别怕，excel vba其实很简单》，上网找了个自动生成询证函的模板看代码。基本看不懂。写出来。求解读
ub Myreset()
Application.StatusBar = False
End Sub
Sub 生成银行询证函()
'功能：按银行分组数据，并复制到独立的工作表
Dim MyArray()
Dim a As Integer
Dim b As Integer
Dim M As Integer
Dim N As Integer
Dim i As Integer
Dim j As Integer
Dim K As String
Dim x1 As Range

For i = Worksheets.Count To 3 Step -1
Application.DisplayAlerts = False
Worksheets(i).Delete
Application.DisplayAlerts = True
Next
Range("J13:L65536").ClearContents
a = Range("a65536").End(xlUp).Row
For i = 13 To a
  Cells(i, 10) = i - 12
Next i

ReDim MyArray(13 To a)
Set x1 = Range(Cells(1, 1), Cells(a, 9))
'MsgBox x1.Cells(1, 1)

For i = 13 To a
MyArray(i) = Cells(i, 1)
Next i

For i = 14 To a
  For j = 13 To i - 1
    If MyArray(i) = MyArray(j) Then
    MyArray(i) = 0
    GoTo 100
    End If
  Next j
100:
Next i
For i = 13 To a
If MyArray(i) <> 0 Then
N = Range("K65536").End(xlUp).Row + 1
Cells(N, 11) = MyArray(i)
Cells(N, 12).Formula = "=COUNTIF(A13:A" & a & ",K" & N & ")"
End If
Next i

For i = a To 13 Step -1
If MyArray(i) <> 0 Then
'k = k + 1
Sheets("询证函模板").Copy After:=Sheets(2)
Sheets(3).Name = i
End If
Next i

For i = 3 To Worksheets.Count
Sheets(i).Select
'MsgBox Sheets(i).Name
K = Sheets(i).Name
For j = 13 To a
   If x1.Cells(j, 1) = x1.Cells(K, 1) Then
   b = Range("a23").End(xlUp).Row + 1
   'MsgBox b
   
   Cells(3, 1) = x1.Cells(j, 1) & "："
   Cells(b, 1) = x1.Cells(j, 2) '户名
   Cells(b, 2) = x1.Cells(j, 3) '帐号
   Cells(b, 4) = x1.Cells(j, 6) '币种
   Cells(b, 5) = x1.Cells(j, 7) '利率
   Cells(b, 6) = x1.Cells(j, 4) '账户类型
   Cells(b, 7) = x1.Cells(j, 8) '余额
   Cells(b, 8) = x1.Cells(j, 5) '起止日期
   Cells(b, 9) = x1.Cells(j, 9) '使用限制
   Cells(b, 10) = x1.Cells(j, 10) '使用限制
   End If
Next j
Next i

For i = 3 To Worksheets.Count
Sheets(i).Select
M = Range("a23").End(xlUp).Row + 1
Rows(M & ":20").Select
Selection.Delete Shift:=xlUp
Range("A3").Select
Next i
Sheets(3).Select
MsgBox "生成完毕！"
End Sub

lmr1107

补充上传附件
研究了一周没搞明白

microyip

全部是很基本的语句，可能你还没学好基础

lmr1107

microyip 发表于 2020-3-1 10:51
全部是很基本的语句，可能你还没学好基础

我把上面的代码一条一条试着拆解
不明白作用和效果的，拆出来写。但是有些还是不明白作用：M = Range("a23").End(xlUp).Row + 1这句，为何是从a23开始向上查询，而不是a65536
还有报错Cells(N, 11) = MyArray(i)已经都定义且赋值了，依然标黄提示错误

microyip

认为没有那么多行就从靠近的一行开始往上检索，提高效率
报错，你就看看是哪部分出的问题了

lmr1107

microyip 发表于 2020-3-1 11:59
认为没有那么多行就从靠近的一行开始往上检索，提高效率
报错，你就看看是哪部分出的问题了

Range("J13:L65536").ClearContents
a = Range("a65536").End(xlUp).Row
For i = 13 To a
  Cells(i, 10) = i - 12
Next i
这段代码我明白。根据A列数据，在第10列填入序号
但是这段代码对于生成询证函的作用是啥呢？辅助列？