Блог

Возможно ли найти первый непустой столбец в определенной строке и вернуть значение в виде числа. Т. е. Возвращает A = 1 , B = 2 и т.д.

С наилучшими пожеланиями

Следующая функция вернет первый заполненный столбец для данного листа и строки (по умолчанию — активный лист и строка 1). Он вернет 0, если ни один столбец не содержит никаких данных.

 Function getFirstCol(Optional ws As Worksheet = Nothing, Optional row As Long = 1) As Long

    If ws Is Nothing Then Set ws = ActiveSheet
    
    If Not IsEmpty(ws.Cells(row, 1)) Then    ' First check the first cell of row
        getFirstCol = 1
    Else
        getFirstCol = ws.Cells(row, 1).End(xlToRight).Column
        ' If no cell is filled in this row, return 0
        If IsEmpty(ws.Cells(row, getFirstCol)) Then getFirstCol = 0 
    End If
End Function
  

Это довольно просто, вы берете ячейку, с которой начинаете, например A1 или B2, и переходите к end (xlRight), вот так:

 Range("A1").End(xlToRight)
  

Column Свойство возвращает номер столбца, поэтому:

 Range("A1").End(xlToRight).Column
  

Удачи

Ответ Range("A1").End(xlToRight).Column предполагает, что ячейка A1 не является пустой.

Вы можете сделать

 =INDEX(A1:C1,MATCH(TRUE,INDEX((A1:C1<>0),0),0)) 
  

Первый ‘Идентификатор столбца’ в строке

Find Метод решения

 Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the 'Column ID' of the first cell (containing data)    '
'               in a given row ("RowNumber") of a given range ("SourceRange"). '
' Inputs                                                                       '
'   RowNumber     The n-th row of the given range. If it is 'out of bounds',   '
'                 the result in Excel is the "#Value!" error, while in VBA     '
'                 the result is "Error 2015". Test accordingly.               '
'   SourceRange   The given range. If omitted, in "Excel" all the cells in     '
'                 the sheet, where the cell containing the formula is located, '
'                 are used, while in "VBA" all the cells in the ActiveSheet    '
'                 are used.                                                    '
'   getColString  FALSE (False) by default when the function will return       '
'                 the 'column number' or 0 (if cell not found).                '
'                 If set to TRUE (True), the 'column string' or ""             '
'                 (if cell not found) is returned.                             '
'   excludeEmpty  FALSE (False) by default when cells containing formulas      '
'                 evaluating to "" are included (otherwise they are not).      '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function getFirstColIDInRow(Optional ByVal RowNumber As Long = 1, _
                            Optional SourceRange As Range, _
                            Optional ByVal getColString As Boolean = False, _
                            Optional ByVal excludeEmpty As Boolean = False) _
         As Variant
    
    ' RowErr1:
    If RowNumber < 1 Then GoTo returnValueError
    ' Define Source Range if it is omitted in the call.
    If SourceRange Is Nothing Then Set SourceRange = ActiveSheet.Cells
    ' RowErr2:
    If RowNumber > SourceRange.Rows.Count Then GoTo returnValueError
    
    ' Define the parameter of the "Lookin" argument of the "Find" method.
    Dim FormVal As XlFindLookIn
    If excludeEmpty Then FormVal = xlValues Else FormVal = xlFormulas
    
    ' Define row range.
    Dim rng As Range: Set rng = SourceRange.Rows(RowNumber)
    ' Define first cell containing data in row range.
    Set rng = rng.Find("*", rng.Cells(1, rng.Columns.Count), FormVal)
    ' Check if no cell is found.
    If rng Is Nothing Then GoTo CellNotFound
    
    ' Choose to return String or Number.
    If getColString Then
        getFirstColIDInRow = Split(rng.Address, "$")(1)
    Else
        getFirstColIDInRow = rng.Column
    End If
    
    Exit Function
    
returnValueError:
    getFirstColIDInRow = CVErr(xlErrValue)
    Exit Function
    
CellNotFound:
    If getColString Then getFirstColIDInRow = "" Else getFirstColIDInRow = 0
    Exit Function
    
End Function

Sub test1()
    
    ' Column number of the 1st cell in the 1st row in the ActiveSheet,
    ' containing data.
    Debug.Print getFirstColIDInRow(1)

    ' Column string of the 1st cell in the 2nd row in Range("A4:E10")
    ' of the ActiveSheet, containing data, 1st cell possibly (all cells might
    ' be blank) being a cell in row 5 (4 being the 1st row).
    Debug.Print getFirstColIDInRow(2, Range("A4:E10"), True)

    ' Column number of the 1st cell in the 3rd row of worksheet "Sheet2",
    ' excluding possible cells evaluating to "".
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet2")
    Debug.Print getFirstColIDInRow(3, ws.Cells, , True)

End Sub
  

Эквиваленты test1 в Excel

 =getFirstColIDInRow(1)
=getFirstColIDInRow(2,A4:E10,TRUE)
=getFirstColIDInRow(3,Sheet2!A:XFD,,TRUE)