Excel VBA将列作为字母

Is there a way to get just the active column as a text to return in a message box?

有没有办法让活动列作为文本返回消息框?

I have this macro that shows a confirmation box,

我有这个宏显示一个确认框,

Dim mycell
mycell = ActiveCell.Address

    response = MsgBox("Are you sure you want to trim column " & mycell & "?", vbYesNo)

If response = vbNo Then
    Exit Sub
End If

`code`

However I want to it ask "Are you sure you want to trim column F" for example, at the moment it will return the cell in the format $F$1. Can I get just the column name?

但是我想问它“你确定要修剪F列”,例如,它将以$ F $ 1格式返回单元格。我可以只获得列名吗?

2 个解决方案

#1

You can use:

您可以使用:

response = MsgBox("Are you sure you want to trim column " & Split(mycell, "$")(1) & "?", vbYesNo)

#2

An alternate to the solution given by @Rory is replacing Split(mycell, "$")(1) by Mid(mycell, 2, 1).

@Rory给出的解决方案的替代方案是将Mid(mycell,2,1)替换为Split(mycell,“$”)(1)。

If ActiveCell.Column > 26 Then
    response = MsgBox("Are you sure you want to trim column " & Mid(mycell, 2, 2) & "?", vbYesNo)
Else
    response = MsgBox("Are you sure you want to trim column " & Mid(mycell, 2, 1) & "?", vbYesNo)
End If

#1