VBA1

Option Explicit
Sub count_end_row(start_row, start_col, end_row)

Dim r As Long

r = ActiveSheet.Rows.count
Cells(r, start_col).End(xlUp).Select

end_row = ActiveCell.Row

End Sub

Sub count_end_col(start_row, start_col, end_col)

Dim c As Long

c = ActiveSheet.Columns.count
Cells(start_row, c).End(xlToLeft).Select

end_col = ActiveCell.Column

End Sub

Sub search_rc_horizon(target_text, start_row, start_col, count, rows_, columns_)

Dim i As Long
Dim j As Long
Dim end_col As Long

end_col = 0
Call count_end_col(start_row, start_col, end_col)
count = 0
j = 1
For i = 1 To end_col
    If Cells(start_row, start_col + i - 1).Value = target_text Then
        rows_(j) = start_row
        columns_(j) = start_col + i - 1
        j = j + 1
        count = count + 1
    Else

    End If

Next

End Sub

Sub test1()
Dim end_row As Long
Dim end_col As Long
Dim start_row As Long
Dim start_col As Long
Dim count As Long
Dim target_text As String

end_row = 0
end_col = 0
start_row = 1
start_col = 1

Call count_end_row(start_row, start_col, end_row)
Call count_end_row(start_row, start_col, end_col)

ReDim rows_(end_row) As Long
ReDim columns_(end_col) As Long

start_row = 1
start_col = 1
target_text = "smp"
end_row = 0

Call search_rc_horizon(target_text, start_row, start_col, count, rows_, columns_)

Dim i As Long

For i = 1 To end_col
    Debug.Print columns_(i)
Next

End Sub