Private Sub InitColumeChars()
colchar(1) = "A"
colchar(2) = "B"
colchar(3) = "C"
colchar(4) = "D"
colchar(5) = "E"
colchar(6) = "F"
colchar(7) = "G"
colchar(8) = "H"
colchar(9) = "I"
colchar(10) = "J"
colchar(11) = "K"
colchar(12) = "L"
colchar(13) = "M"
colchar(14) = "N"
colchar(15) = "O"
colchar(16) = "P"
colchar(17) = "Q"
colchar(18) = "R"
colchar(19) = "S"
colchar(20) = "T"
colchar(21) = "U"
colchar(22) = "V"
colchar(23) = "W"
colchar(24) = "X"
colchar(25) = "Y"
colchar(26) = "Z"End Sub
我需要用到更多单元,在程序中我会用到strEnd = colchar(iCNum + 3) + CStr(iRNum + 3)
等号后面可以定位一个单元格,iCNum,iRNum是整数,现在我要在上面的函数中加
colchar(27)="AA"就不可以了,怎么办?我回去再看看,由于时间很紧我怕万一想不出来,所以请大家帮忙,以便在规定时间完成任务,谢谢!
colchar(1) = "A"
colchar(2) = "B"
colchar(3) = "C"
colchar(4) = "D"
colchar(5) = "E"
colchar(6) = "F"
colchar(7) = "G"
colchar(8) = "H"
colchar(9) = "I"
colchar(10) = "J"
colchar(11) = "K"
colchar(12) = "L"
colchar(13) = "M"
colchar(14) = "N"
colchar(15) = "O"
colchar(16) = "P"
colchar(17) = "Q"
colchar(18) = "R"
colchar(19) = "S"
colchar(20) = "T"
colchar(21) = "U"
colchar(22) = "V"
colchar(23) = "W"
colchar(24) = "X"
colchar(25) = "Y"
colchar(26) = "Z"End Sub
我需要用到更多单元,在程序中我会用到strEnd = colchar(iCNum + 3) + CStr(iRNum + 3)
等号后面可以定位一个单元格,iCNum,iRNum是整数,现在我要在上面的函数中加
colchar(27)="AA"就不可以了,怎么办?我回去再看看,由于时间很紧我怕万一想不出来,所以请大家帮忙,以便在规定时间完成任务,谢谢!
'把正整数转化成excel中的列代码,如27->"AA"
'适用列代码"ZZ"以内,若超出,要设置循环,但一般够用了
Function IntToCol(Inta As Integer) As String
Dim a, b As Integer
Dim sa, sb As String
a = Int(Inta / 26)
b = Inta Mod 26
If b <> 0 And a = 0 Then
sa = ""
sb = Chr(b + 64)
ElseIf b <> 0 And a <> 0 Then
sa = Chr(a + 64)
sb = Chr(b + 64)
End If
If b = 0 And a - 1 = 0 Then
sa = ""
sb = "Z"
ElseIf b = 0 And a - 1 <> 0 Then
sa = Chr(a - 1 + 64)
sb = "Z"
End If
IntToCol = sa & sb
End FunctionSub f()
MsgBox IntToCol(27) '显示"AA"
End Sub
Dim i As Long
Dim a(1 To 26) As String
Dim strResult As String
Dim intMOD As Long
Dim intMul As Long
If intVal <> 0 Then
For i = 1 To 26
a(i) = Chr(64 + i)
Next i
intMul = intVal \ 26
intMOD = intVal Mod 26
If intMul <> 0 Then
If intMul > 26 Then
strResult = strResult & Twenty_Six(intMul)
Else
strResult = strResult & a(intMul)
End If
If intMOD > 26 Then
strResult = strResult & Twenty_Six(intMOD)
Else
strResult = strResult & a(intMOD)
End If
Else
strResult = strResult & a(intVal)
End If
Twenty_Six = strResult
Else
Twenty_Six = "0 Error"
End If
End FunctionPrivate Sub Command1_Click()
Debug.Print Twenty_Six(CLng(Val(Me.Text1.Text)))
End Sub
你的程序忽略了"Z"、"AZ"、"BZ"、"CZ"……的特殊性,
换言之26的倍数在你的程序中全都通不过,不知健壮在哪里?这么重要的边缘数据测试时怎么可以漏掉呢?:)
对于EXCEL的特点,列数不超过256,我写得完全够用了。
我觉得写程序后更重要的就是选择有效的数据进行测试,这很重要。
但忘了怎么转-_-b
如果只是为了能用数字找到相应的列,用这个vba代码就可以了
Cells(3, 4).Select '选中D3单元格
Dim i As Long
Dim a(1 To 26) As String
Dim strResult As String
Dim intMOD As Long
Dim intMul As Long
If intVal <> 0 Then
For i = 1 To 26
a(i) = Chr(64 + i)
Next i
intMul = intVal \ 26
intMOD = intVal Mod 26
If intMul <> 0 Then
If intMOD <> 0 Then
If intMul > 26 Then
strResult = strResult & Twenty_Six(intMul)
Else
strResult = strResult & a(intMul)
End If
If intMOD > 26 Then
strResult = strResult & Twenty_Six(intMOD)
Else
strResult = strResult & a(intMOD)
End If
Else
strResult = strResult & Twenty_Six(intMul - 1)
strResult = strResult & "Z"
End If
Else
strResult = strResult & a(intVal)
End If
Twenty_Six = strResult
Else
Twenty_Six = ""
End If
End Function