VBA根据输入值查找下一列 [英] VBA Finding the next column based on an input value
问题描述
在我现在想写的程序中，我拿两列数字并对它们进行计算。在用户告诉我（他们在工作簿中的单元格中输入我的代码所在的列值）之前，我不知道这两列的位置。
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).
例如，如果用户输入A和B作为所有信息所在的列，则可以基于这些值执行计算。同样，如果他们想要分析另一个工作表（或工作簿），并且列是在F和G他们可以输入那些。问题是我要求用户输入这两列以及其他四列（最后四个是结果列）。我做到了这一点，希望我能够使这个灵活，但是现在的灵活性是可以接受的。
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.
我的问题是，如果我被赋予了一些值信息将会（我们说F），如何知道这个列在输入值之后或之前的列。所以如果我只给了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.
下面是变量工作原理的例子在我需要做这个新的问题之前：
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
这里，单元格B2 - B4是用户输入值的位置。一般来说，我想要能够不再有B3和B4。我觉得Offset（0,1）可能有所帮助，但到目前为止我一直无法正确实现。
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.
谢谢，
Jesse Smothermon
Jesse Smothermon
推荐答案
这里有两个功能可以帮助您处理列>Z。它们将列的文本形式转换为列索引（作为Long值），反之亦然：
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:
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)
相应地编辑了mwolfe02的评论：
EDITED accordingly to the remark of mwolfe02:
当然，如果您对列名不感兴趣，但只想在用户给定的列之下的给定行中获取特定单元格的范围对象，则此代码为overkill。在这种情况下，一个简单的
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
将会执行。
这篇关于VBA根据输入值查找下一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
