Excel VBA:我们可以通过名称来引用列吗? [英] Excel VBA: Can we refer a column by its name?

问题描述

我想通过其标题名称引用一列.

I want to refer a column by its header name.

当前列为第4列，标题名称为首选项". 并且该列由是"或否"组成

Currently column is 4th one and header name is "Preference". And the column consists of "Yes" or "No"

第5列标题为原因" 并且仅在首选项"列为否"时填充

5th column header is "Reason" And it is filled only when "Preference" column is "No"

我的代码是

Private Sub CommandButton1_Click()

Dim i As Integer
Dim MyWorksheetLastRow As Byte
Dim MyWorksheetLastColumn As Byte

MyWorksheetLastRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
MyWorksheetLastColumn = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets(1).Cells(1, MyWorksheetLastColumn + 1).Value = "Result"
For i = 2 To MyWorksheetLastRow
If Cells(i, 4).Value = "Yes" Then
Cells(i, MyWorksheetLastColumn + 1).Value = Cells(i, 4).Value
Else: Cells(i, MyWorksheetLastColumn + 1).Value = Cells(i, 5).Value
End If
Next i

End Sub

我想要的是代替Cells(i，4)，我想通过列标题示例来调用它:Cells(i，"Preference"). 因为我之前不会列出首选项"的列号.我使用excel vba，因为我必须处理20-30个类似的文件.

What I want is instead of Cells(i,4) , I want to call the it by column header example: Cells(i,"Preference"). Because I won't the column number of "Preference" in prior. And I use excel vba because I have to deal 20-30 similar files.

推荐答案

根据我的评论，如果要直接进行操作，则必须这样做:

Further to my comments, if you want to do it direct, you would have to do this:

cells(i,Application.WorksheetFunction.Match("Preference", Range("1:1"), 0)).

这篇关于Excel VBA:我们可以通过名称来引用列吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！