Выполнить функцию VBA для столбца с неизвестным количеством строк

#excel #vba

Вопрос:

Я пытаюсь создать функцию, с помощью которой различные пользователи могли бы выполнять приведенную ниже функцию для столбца, в котором будет разное количество строк для каждого пользователя, но я не могу понять, как учесть вариативность количества строк.

 Function MKT(ByVal Temperatures As Variant) As Double
Dim Sum As Double
Dim TemperatureCount As Long
Dim GasConst As Double
Dim DeltaH As Double
Dim Conv As Double
Conv = 273.15
GasConst = 8.314472
DeltaH = 10 * GasConst
Sum = 0
For TemperatureCount = Temperatures.Cells.Count To 1 Step -1
        Sum = Sum   Exp(-DeltaH / (GasConst * (Temperatures(TemperatureCount)   Conv)))
        Next TemperatureCount
        MKT = (DeltaH / GasConst) / (-Log(Sum / Temperatures.Cells.Count)) - Conv
End Function

Функция работает, если я задаю значение ячейки =MKT(A1: A32557), но не если я задаю =MKT(A: A).

Я думаю, это могло бы помочь, но я не уверен, как это реализовать:

 Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Кроме того, возможно ли смещение значений на 1 строку для учета заголовка?

1. Используемый диапазон не всегда актуален; безопаснее полагаться на значения ячеек. В идеале у вас должен быть столбец, который будет содержать значение для каждой строки, с которой должна работать функция, и содержать значения только для этих строк; если нет, некоторый вариант End (xlUp) должен работать до тех пор, пока у вас также нет заполненных ячеек под этой таблицей.

Ответ №1:

или, чтобы избежать жесткого кодирования ограничения строк, используйте

 with sheet1
  lastRowColA = .Range("A" amp; .Rows.Count).End(xlUp).row
  Set rng = .range("A2:A" amp; lastRowColA)
end with

Ответ №2:

 lastRowColA = Range("A65536").End(xlUp).Row
For Each cell In Range("a2:a" amp; lastRowColA)
'MsgBox cell
Next cell

Ответ №3:

Вы можете создать подмножество температур, пересекаясь с Используемым диапазоном

 Dim SubRange as Range
Set SubRange=Intersect(Temperatures,Temperatures.Parent.UsedRange)

или, как опубликовали nick и osknows, вы можете использовать .End (xlUp), но обратите внимание, что при этом игнорируются скрытые ячейки

 Function SubRange(theRange As Range) As Range
Dim LastRow As Long
LastRow = theRange.Parent.Cells(theRange.Parent.Rows.Count, theRange.Column).End(xlUp).Row
Set SubRange = theRange.Resize(LastRow - theRange.Row   1)
End Function