how to use the ampersand symbol (&) like in VBA while in Excel

Question

I need to SUM in excel, but the range is always difference, need to reference to cell to get the range number. Is there anyway to use the & in excel to point to that cell to get the value for the range?

Answer 1

So, you can first reference the cell value based on which you would to select range for sum. Let's assume Range("A1") has the value till where you would like to sum. Use the below code and adjust accordingly.

Sub sum_values()
Dim i as long
i = Range("A1").value 'put here the cell you would like to reference.

'Let's assume you want the sum in Range("B1")
Range("B1").value = Worksheetfunction.sum(Range("A2:A" & i)

End sub

Let me know if you need any further clarification.