如何获取具有已知列标题名称的列值 [英] How to get column value with known column header name
本文介绍了如何获取具有已知列标题名称的列值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
说我有一组给定的列标题,并且我只知道列标题名称. 我只想检索列名,例如,我正在搜索列名"Text",它位于单元格A中,那么我想将A作为结果,但它给了我1.有人可以帮我吗.
Say I have a given set of column headers and I know only the column header name. I just want to retrieve the column name e.g I'm searching for the column name "Text" and it is located in cell A, then I want A as my result but its giving me 1. Can anyone help me out.
这是我的代码
Sub searchHeader()
columnNamesRow = 1 ' or whichever row your names are in
nameToSearch = "Text" ' or whatever name you want to search for
columnToUse = 0
lastUsedColumn = Worksheets("Sheet1").Cells(1, Worksheets("Sheet1").Columns.Count).End(xlToLeft).Column
For col = 1 To lastUsedColumn
If Worksheets("Sheet1").Cells(columnNamesRow, col).Value = nameToSearch Then
columnToUse = col
End If
Next col
If columnToUse > 0 Then
' found the column you wanted, do your thing here using "columnToUse" as the column index
MsgBox columnToUse
End If
End Sub
推荐答案
@newguy指出的功能就是答案
the function @newguy pointed is the answer
无论如何,它可以帮助您考虑:
anyhow it may help you to consider:
-
使用
Range
对象的Find()
方法,而不是遍历单元格
use
Find()
method ofRange
object instead of looping through cells
使用该功能的核心满足您的特定需求
use the core of that function for your specific needs
像这样:
Option Explicit
Function searchHeader(shtName As String, nametosearch As String, headersRow As Long) As String
Dim f As Range
With Worksheets(shtName)
Set f = .Range(.Cells(headersRow, 1), .Cells(headersRow, .Columns.Count).End(xlToLeft)).Find(what:=nametosearch, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not f Is Nothing Then searchHeader = Split(f.Address, "$")(1)
End Function
将按以下方式使用:
Sub main()
MsgBox """Text"" is the header of column """ & searchHeader("Sheet1", "Text", 1) & """"
End Sub
这篇关于如何获取具有已知列标题名称的列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文