#excel #vba
#excel #vba
Вопрос:
Option Explicit
Function FindIndexCol(rngIndexRow, rngIndexCol) As String
Set rngIndexRow = Worksheets("sheet1").Range(rngIndexRow)
Set rngIndexCol = rngIndexRow.Find(rngIndexCol)
FindIndexCol = Split(Cells(1, rngIndexCol.Column).Address, "$")(1)
End Function
Sub Test()
Dim Purchasing_Document, Backup_Purchasing_Document As String
Purchasing_Document = FindIndexCol("1:1", "Purchasing Document")
Backup_Purchasing_Document = FindIndexCol("1:1", "Backup Purchasing Document")
End Sub
Я использую FindIndexCol()
функцию для получения индекса столбца по имени. Purchasing_Document
должен вернуть мне столбец A и Backup_Purchasing_Document
вернет мне столбец K. Но оба они возвращают мне символ K. Как я должен изменить приведенный выше код?
Ответ №1:
Обратите внимание, что если вы объявляете Dim Purchasing_Document, Backup_Purchasing_Document As String
только последнюю переменную, то a String
, а первая — a Variant
. В VBA вам нужно указать тип для каждой переменной : Dim Purchasing_Document As String, Backup_Purchasing_Document As String
.
Документация по диапазону.Поиск состояний метода:
Настройки для
LookIn
,LookAt
,SearchOrder
, иMatchByte
сохраняются каждый раз, когда вы используете этот метод. Если вы не укажете значения для этих аргументов при следующем вызове метода, будут использованы сохраненные значения.
Поэтому, если вы используете Find
, вы должны, по крайней мере, указать эти 4 параметра, иначе вы не сможете предсказать, какая настройка Find
используется для этих параметров.
Также LookAt:=xlWhole
необходимо различать "Purchasing Document"
и "Backup Purchasing Document"
, потому что первое является частью второго.
Так что, по крайней мере, выполните следующие действия:
Public Function FindIndexCol(ByVal IndexRow As String, ByVal IndexCol As String) As String
Dim rngIndexCol As Range
Set rngIndexRow = Worksheets("sheet1").Range(IndexRow)
Dim rngIndexCol As Range
Set rngIndexCol = rngIndexRow.Find(What:=IndexCol, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchByte:=False)
If Not rngIndexCol Is Nothing Then
FindIndexCol = Split(Cells(1, rngIndexCol.Column).Address, "$")(1)
Else
'output some error if nothing was found
MsgBox "Could not find '" amp; IndexCol amp; "' in '" amp; IndexRow amp; "'.", vbCritical
'or return some error at least
'FindIndexCol = "Column not found"
End If
End Function
Public Sub Test()
Dim Purchasing_Document As String, Backup_Purchasing_Document As String
Purchasing_Document = FindIndexCol("1:1", "Purchasing Document")
Backup_Purchasing_Document = FindIndexCol("1:1", "Backup Purchasing Document")
End Sub
Обратите внимание, что FindIndexCol
работает только sheet1
из-за этой строки
Set rngIndexRow = Worksheets("sheet1").Range(rngIndexRow)
Поэтому я предлагаю сделать его более полезным, сделав его более общим
Public Function FindIndexCol(ByVal rngIndexRow As Range, ByVal ColumnName As String) As String
Dim rngIndexCol As Range
Set rngIndexCol = rngIndexRow.Find(What:=ColumnName, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchByte:=False)
If Not rngIndexCol Is Nothing Then
FindIndexCol = Split(rngIndexRow.Parent.Cells(1, rngIndexCol.Column).Address, "$")(1)
Else
'output some error if nothing was found
MsgBox "Could not find '" amp; ColumnName amp; "' in '" amp; rngIndexRow.Address(External:=True) amp; "'.", vbCritical
'or return some error at least
'FindIndexCol = "Column not found"
End If
End Function
Public Sub Test()
Dim Purchasing_Document As String, Backup_Purchasing_Document As String
With Worksheets("sheet1")
Purchasing_Document = FindIndexCol(.Rows(1), "Purchasing Document")
Backup_Purchasing_Document = FindIndexCol(.Rows(1), "Backup Purchasing Document")
End With
End Sub
Комментарии:
1. Спасибо, PEH, общедоступная функция — действительно хорошая идея для меня использовать эту функцию среди всех листов.
2. @Gunhanwei Я понял, что что-то напутал с объявлениями типов переменных. Пожалуйста, проверьте мой отредактированный ответ, где я это исправил!
3. @PEH Я только что попробовал второй метод, и он выдает какую-то ошибку
object doesn't support property or method
и выделяет строкуPurchasing_Document = FindIndexCol(.Row(1), "Purchasing Document")
4. @Gunhanwei извините, что я виноват
.Row(1)
, должно быть.Rows(1)
. Я исправил опечатку.