[技术随笔]VBA中根据单元格列索引查找对应列的字母.docxVIP

[技术随笔]VBA中根据单元格列索引查找对应列的字母 阅前提示：本文章主要讲解如何在Excel文档中，使用VBA通过列的索引查找该列对应的字母。例如：输入数字1，则返回第一列的对应字母“A”。在本文章中设定：Excel文档中列的索引从1开始，即第一列为“A”。如下图： 图1 文章中约定的 “定语”（此处的“定语”为作者根据文章需要二自己创建的短语，并非具有权威或真实存在的）： 列字母：Excel中列所对应的字母 列索引：在Excel中的第几列 之所以写下这篇文章，主要是因为在最近的工作中出现了此需求：给出一个值，在一千多列中找到这个值所在的列，然后在该列下制定的单元格填写内容。以图1为例，给出“32，将“32所在列的地2行的单元格填写内容“1”。根据本文章接下来要讲解的方法，可以得到“32”对应的列为“AF”，那么，应该在“AF2”这个单元格里填写“1”。由于数据量大（70多万条数据），所以需要通过VBA来实现。 在Excel中，列对应的字母是从字母A到字母Z顺序分配的；一个字母的分配完了在从两个字母的开始分配，如：AA、AB…ZZ；两个字母的分配完了在从三个字母的开始分配，如：AAA、AAB…XFD。在Excel2013中列最多只有16384，对应的字母为XFD。所以，处理的方法就是循环使用列索引对26求余，每循环一次会得到一个余数，根据这个余数在数组中得到对应的字母，那么列字母=字母+列字母。在求余结束之后要判断余数是否为0，如果为零，表示刚好整除26，此时的列字母=“Z”+列字母，余数要增加1，即余数=0+1；否则，列字母=数组[余数]+列字母。然后，列索引=（列索引-余数）、26。到此，一次循环结束，进入下一次循环。循环结束的条件为列索引大于0。 在VBA中，两个整数相除，如果结果有小数，则会4舍6入,5看奇偶。就是说，如果小数的第一位小于等于4，则直接舍掉，去整数，如：10/3=1.33333，舍掉之后得到的是1。如果小数的第一位大于等于6，则会舍掉小数，然后将整数部分增加1，如：2/3=0.666666…..，舍掉之后是0+1=1。如果小数部分第一位等于5，那么还要看整数部分是奇数还是偶数。如果整数部分是奇数，则得到的结果应该是整数部分+1，如：3/2=1.5，舍掉之后是1+1=2；如果舍掉之后的整数部分是偶数，则得到的结果就是整数部分，如9/2=4.5，舍掉之后就是4。为什么要在这里提出这一点呢？因为在循环的过程中需要用列索引除以26并且结果必须为整数，所以需要写个方法来实现这个功能： Function InitFormat(num) Dim i As Long Dim pointIndex As Integer pointIndex = InStr(CStr(num), .) If pointIndex 0 Then i = Mid(CStr(num), 1, pointIndex - 1) Else i = num End If InitFormat = i End Function 该方法理解起来很简单：首先对传入进来的数进行判断，是否有小数点，如果没有，说明传进来的是一个整数，直接返回；如果有，则换换成字符串之后截取整数部分，然后在返回。 接下来就是主要的代码了： Function FindCellName(cellIndex) Dim cellName As String Dim r2 As Integer Dim arr() arr = [{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z}] cellName = Do While (cellIndex 0) r2 = cellIndex Mod 26 If r2 = 0 Then cellName = arr(26) + cellName r2 = 1 Else cellName = arr(r2) + cellName End If cellIndex = InitFormat((cellIndex - r2) / 26) Loop FindCellName = cellName End