This function accepts a range of cells such as A1:F1 just fine, but if I try to press the control key and select difference cells like A1,A5,A23,A43 and then use that as myRange it does not work. I understand this on a fundamental level because that is not a range per se, but how could I rewrite this finction so that it could take in arguments like A1,A5,A23,A43 ?
Could anyone also explain a bit about what exactly would need to change??
Thank you!!
Function PriceChange(myRange As Range) As Variant
Dim cell As Range
Dim LastValue As Double
Dim NextToLastValue As Double
Dim CellCount As Long
For Each cell In myRange
If Len(cell) > 0 Then
CellCount = CellCount + 1
If CellCount > 1 Then NextToLastValue = LastValue
LastValue = cell.Value
End If
Next cell
If CellCount > 1 Then
PriceChange = LastValue - NextToLastValue
Else
PriceChange = ""
End If
End Function
Could anyone also explain a bit about what exactly would need to change??
Thank you!!
Function PriceChange(myRange As Range) As Variant
Dim cell As Range
Dim LastValue As Double
Dim NextToLastValue As Double
Dim CellCount As Long
For Each cell In myRange
If Len(cell) > 0 Then
CellCount = CellCount + 1
If CellCount > 1 Then NextToLastValue = LastValue
LastValue = cell.Value
End If
Next cell
If CellCount > 1 Then
PriceChange = LastValue - NextToLastValue
Else
PriceChange = ""
End If
End Function