选择区域中填充单元格地址（不允许循环单元格）

作者：绿色风分类：时间：2022-08-18 浏览：98

楼主
wqfzqgk

1、用公式法：

Sub test()
Selection.Formula = "=CELL(""address"",RC)"
Dim arr
a = Selection.Columns.Count
ReDim arr(1 To Selection.Rows.Count, 1 To a)
arrr = Selection.Value
For i = 1 To Selection.Rows.Count
For ii = 1 To Selection.Columns.Count
arr(i, ii) = Split(arrr(i, ii), "$")(1) & Split(arrr(i, ii), "$")(2)
Next
Next
Selection = arr
End Sub

2、一自定义函数法:

Public Function 数值转列号(ByVal 数值 As Long) As String
Dim a As String
arr = Array("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")
数值转列号 = ""
Do While 数值 > 0
a = CStr(数值 Mod 26)
If a = 0 Then
a = arr(25)
数值转列号 = a & 数值转列号
数值 = (数值 - 26) / 26
Else
a = arr(a - 1)
数值转列号 = a & 数值转列号
数值 = Int(数值 / 26)
End If
Loop
End Function
Sub 区域填充单元格地址不用循环单元格()
Dim arr(), ii, i
rw1 = Selection(1, 1).Row
rw2 = Selection(1, 1).Row + Selection.Rows.Count - 1
cl1 = Selection(1, 1).Column
cl2 = Selection(1, 1).Column + Selection.Columns.Count - 1
ReDim arr(rw1 To rw2, cl1 To cl2)
For i = cl1 To cl2
For ii = rw1 To rw2
arr(ii, i) = 数值转列号(i) & ii
Next
Next
Selection = arr
End Sub

2楼
罗刚君

Selection.Formula = "=address(row(),column(),4)"
Selection = Selection.Value

免责声明

有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，经与ExcelTip.Net站长Apolloh商议并征得其同意，现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，供有需要的人士查询使用，也慰缅曾经的论坛时代。所示各个帖子的原作者如对版权有异议，可与本人沟通提出，或于本站点留言，我们会尽快处理。在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一

评论列表