VBA 根据输入值查找下一列

VBA Finding the next column based on an input value

提问by Jesse Smothermon

In a program that I'm trying to write now I take two columns of numbers and perform calculations on them. I don't know where these two columns are located until the user tells me (they input the column value in a cell in the workbook that my code is located in).

在我现在尝试编写的程序中，我取两列数字并对其进行计算。我不知道这两列的位置，直到用户告诉我（他们在我的代码所在的工作簿的单元格中输入列值）。

For example, if the user inputted "A" and "B" as the columns where all the information is in I can perform calculations based on those values. Likewise if they wanted to analyze another worksheet (or workbook) and the columns are in "F" and "G" they could input those. The problem is that I'm asking the user to input those two columns as well as four others (the last four are the result columns). I did this in hopes that I would be able to make this flexible, but now inflexibility is acceptable.

例如，如果用户输入“A”和“B”作为所有信息所在的列，我可以根据这些值执行计算。同样，如果他们想分析另一个工作表（或工作簿）并且列在“F”和“G”中，他们可以输入这些。问题是我要求用户输入这两列以及其他四列（最后四列是结果列）。我这样做是希望我能够使它变得灵活，但现在不灵活是可以接受的。

My question is, if I'm given a value of where some information will be (let's say "F") how can I figure out what the column will be after or before that inputted value. So if I'm only given "F" I'll be able to create a variable to hold the "G" column.

我的问题是，如果我获得了某些信息所在位置的值（假设为“F”），我如何确定该列在输入值之后或之前的位置。因此，如果我只得到“F”，我将能够创建一个变量来保存“G”列。

Below are examples of how the variables worked before I needed to do this new problem:

以下是在我需要解决这个新问题之前变量如何工作的示例：

Dim first_Column As String
Dim second_Column As String
Dim third_Column As String

first_Column = Range("B2").Text
second_Column = Range("B3").Text
third_Column = Range("B4").Text

Here the cells B2 - B4 are where the user inputs the values. Generally I want to be able to not have the B3 and B4 anymore. I feel like the Offset(0,1) might be able to help somehow but so far I've been unable to implement it correctly.

这里的单元格 B2 - B4 是用户输入值的地方。一般来说，我希望不再拥有 B3 和 B4。我觉得 Offset(0,1) 可能会以某种方式提供帮助，但到目前为止我一直无法正确实现它。

Thank you,

谢谢，

Jesse Smothermon

杰西·斯莫瑟蒙

回答by mwolfe02

You were on the right track with Offset. Here is a test function that shows a couple different approaches to take with it:

你在正确的轨道上Offset。这是一个测试函数，显示了几种不同的方法：

Sub test()

Dim first_Column As String
Dim second_Column As String
Dim third_Column As String
Dim r As Range

    first_Column = Range("B2").Text
    second_Column = Range("B2").Offset(1, 0).Text
    third_Column = Range("B2").Offset(2, 0).Text
    Debug.Print first_Column, second_Column, third_Column

    Set r = Range("B2")
    first_Column = r.Text
    Set r = r.Offset(1, 0)
    second_Column = r.Text
    Set r = r.Offset(1, 0)
    third_Column = r.Text
    Debug.Print first_Column, second_Column, third_Column

End Sub

UPDATE: After re-reading your question I realize you were trying to do offsets based on a user-entered column letter. @rskar's answer will shift the column letter, but it will be a lot easier to work with the column numberin code. For example:

更新：重新阅读您的问题后，我意识到您正在尝试根据用户输入的列字母进行偏移。@rskar 的回答将移动列字母，但在代码中使用列号会容易得多。例如：

Sub test()
Dim first_Col As Integer, second_Col As Integer
    first_Col = Cells(, Range("B2").Text).Column
    second_Col = first_Col + 1

    Cells.Columns(first_Col).Font.Bold = True
    Cells.Columns(second_Col).Font.Italic = True
End Sub

回答by Doc Brown

Here are two functions that will help you dealing with columns > "Z". They convert the textual form of a column to a column index (as a Long value) and vice versa:

这里有两个函数可以帮助您处理列 > "Z"。它们将列的文本形式转换为列索引（作为 Long 值），反之亦然：

Function ColTextToInt(ByVal col As String) As Long
    Dim c1 As String, c2 As String
    col = UCase(col) 'Make sure we are dealing with "A", not with "a"
    If Len(col) = 1 Then  'if "A" to "Z" is given, there is just one letter to decode
        ColTextToInt = Asc(col) - Asc("A") + 1
    ElseIf Len(col) = 2 Then
        c1 = Left(col, 1)  ' two letter columns: split to left and right letter
        c2 = Right(col, 1)
        ' calculate the column indexes from both letters  
        ColTextToInt = (Asc(c1) - Asc("A") + 1) * 26 + (Asc(c2) - Asc("A") + 1)
    Else
        ColTextToInt = 0
    End If
End Function

Function ColIntToText(ByVal col As Long) As String
    Dim i1 As Long, i2 As Long
    i1 = (col - 1) \ 26   ' col - 1 =i1*26+i2 : this calculates i1 and i2 from col 
    i2 = (col - 1) Mod 26
    ColIntToText = Chr(Asc("A") + i2)  ' if i1 is 0, this is the column from "A" to "Z"
    If i1 > 0 Then 'in this case, i1 represents the first letter of the two-letter columns
        ColIntToText = Chr(Asc("A") + i1 - 1) & ColIntToText ' add the first letter to the result
    End If
End Function

Now your problem can be solved easily, for example

现在您的问题可以轻松解决，例如

newColumn = ColIntToText(ColTextToInt(oldColumn)+1)

EDITED accordingly to the remark of mwolfe02:

根据 mwolfe02 的评论编辑：

Of course, if you are not interested in the column names, but just want to get a range object of a specific cell in a given row right beneath a column given by the user, this code is "overkill". In this case, a simple

当然，如果您对列名不感兴趣，而只想在用户给定的列正下方的给定行中获取特定单元格的范围对象，则此代码是“矫枉过正”。在这种情况下，一个简单的

 Dim r as Range
 Dim row as long, oldColumn as String
 ' ... init row and oldColumn here ...

 Set r = mysheet.Range(oldColumn & row).Offset(0,1)
 ' now use r to manipulate the cell right to the original cell

will do it.

会做的。

回答by James Skidmore

There are a few syntactical problems with @rskar's answer. However, it was helpful in producing a function that grabs a column "letter", based on an input column "letter" and a desired offset to the right:

@rskar 的回答存在一些语法问题。但是，它有助于根据输入列“字母”和所需的右侧偏移量生成一个获取“字母”列的函数：

Public Function GetNextCol(TheCol As String, OffsetRight As Integer) As String
    Dim TempCol1 As String
    Dim TempCol2 As String
    TempCol1 = Range(TheCol & "1").Address
    TempCol2 = Range(TempCol1).Offset(0, OffsetRight).Address(0, 0, xlA1)
    GetNextCol = Left(TempCol2, Len(TempCol2) - 1)
End Function

回答by rskar

In light of the comments of others (and they all raised valid points), here is a much better solution to the problem, using Offsetand Address:

根据其他人的评论（他们都提出了有效的观点），这里有一个更好的解决方案，使用Offsetand Address：

Dim first_Column As String
Dim second_Column As String
Dim p As Integer

first_Column = Range("B2").Text

second_Column = _
    Range(first_Column + ":" + first_Column).Offset(0, 1).Address(0, 0, xlA1)
p = InStr(second_Column, ":")
second_Column = Left(second_Column, p - 1)

The above should work for any valid column name, "Z" and "AA" etc. included.

以上应该适用于任何有效的列名，包括“Z”和“AA”等。

回答by rskar

Make use of the Asc() and Chr() functions in VBA, like so:

在 VBA 中使用 Asc() 和 Chr() 函数，如下所示：

Dim first_Column As String
Dim second_Column As String

first_Column = Range("B2").Text
second_Column = Chr(Asc(first_Column) + 1)

The Asc(s)function returns the ASCII code (in integer, usually between 0 and 255) of the first character of a string "s".

该Asc(小号)函数返回ASCII代码（在整数，通常是0到255之间）的字符串“S”的第一个字符的。

The Chr(c)function returns a string containing the character which corresponds to the given code "c".

的Chr(Ç)函数返回一个包含字符，其对应于给定的代码“C”的字符串。

Upper case letters (A thru Z) are ASCII codes 65 thru 90. Just google ASCII for more detail.

大写字母（A 到 Z）是 65 到 90 的 ASCII 代码。只需谷歌 ASCII 了解更多详细信息。

NOTE: The above code will be fine so long as the first_Column is between "A" and "Y"; for columns "AA" etc., it will take a little more work, but Asc() and Chr() will still be the ticket to coding for that.

注意：只要 first_Column 在“A”和“Y”之间，上面的代码就可以了；对于列“AA”等，需要做更多的工作，但 Asc() 和 Chr() 仍将是为此编码的票证。