# Getting list from range in formula

#### aalansari

##### New Member
Example: To calculate the sum of values in the range of A1:A10 I can use
Excel Formula:
``=SUM(A1:A8)``
or I can use
Excel Formula:
``=SUM(A1,A2,A3,A4,A5,A6,A7,A8)``
I'd like to convert any range in a formula (e.g. A1:A10) to a list formart (i.e., A1,A2,An)...

I used VBA to do and it sort of works; however, there are a number of limitations:
1. I can't use more than 1 function in a cell (e.g.
Excel Formula:
``=SUM(A1:A10)/COUNT(A1:A5)``
)
2. It breaks when I use paranthesies before my formula (e.g.
Excel Formula:
``=(B1*B2)/SUM(A1:A10)``
)

I feel like I might be overcomplicating it and thought of asking if there was a better way...

Here's what I have so far:

VBA Code:
``````Sub conv()

Dim x, start_w, end_w, refr, refr2, lst As String

'get formula from cell without "="
x = Sheet1.Range("B1").Formula 'example "=SUM(A1:A8)"

'Find the position of ":" which is equivalent to a range
t = Application.WorksheetFunction.Find(":", x)
'Begin of function
Z = Application.WorksheetFunction.Find("(", x)
'Find reference cells
form_strt = Left(x, Z)
ref_strt = Mid(x, t - 2, 2) 'Starting Reference (i.e., A1)
ref_end = Mid(x, t + 1, 4) 'Ending Reference (i.e., A8)

'Remove non-numeric characters from starting reference
start_w = ""
For k = 1 To Len(ref_strt)
refr = Mid(ref_strt, k, 1)
If Asc(refr) >= 48 And _
Asc(refr) <= 57 Then
start_w = start_w & refr
End If
Next
'Remove non-numeric characters from ending reference
end_w = ""
For k = 1 To Len(ref_end)
refr = Mid(ref_end, k, 1)
If Asc(refr) >= 48 And _
Asc(refr) <= 57 Then
end_w = end_w & refr
End If
Next
'Column Reference
ref_col = Left(ref_strt, 1) 'example Column "A"

'Create a list
lst = ""
For i = 1 To CInt(end_w)
If i <> CInt(end_w) Then
lst = lst & ref_col & i & ", "
Else
lst = lst & ref_col & i
End If
Next i

'Return list
Sheet1.Range("B2").Value = form_strt & lst & ")"

End Sub``````

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### rlv01

##### Well-known Member
A different approach. This works for all 3 of your examples.

VBA Code:
``````Sub conv()
Dim x, SA
Dim NewFormula As String, RList As String, S As String
Dim I As Long

'get formula from cell without "="
x = Sheet1.Range("B1").Formula 'example "=SUM(A1:A8)"
NewFormula = x

SA = Split(Replace(Replace(x, "(", "|"), ")", "|"), "|")

For I = LBound(SA) To UBound(SA)
S = SA(I)
If (InStr(S, ":") > 1) And (InStr(S, ":") < Len(S)) Then
RList = RngExpand(S) 'convert to list
If RList <> "Error" Then
NewFormula = Replace(NewFormula, S, RList)
End If
End If
Next I
'Return list
Sheet1.Range("B2").Value = NewFormula
End Sub

Function RngExpand(RngStr As String) As String
Dim R As Range, CellRng As Range
Dim S As String

On Error Resume Next
Set CellRng = ActiveSheet.Range(RngStr)
On Error GoTo 0

If Not CellRng Is Nothing Then
S = ""
For Each R In CellRng
S = S & R.Address(False, False) & ","
Next R
If S <> "" Then
S = Left(S, Len(S) - 1)
End If
RngExpand = S
Else
RngExpand = "Error"
End If
End Function``````

#### aalansari

##### New Member
This is exactly what I was looking for! Thanks for your help

#### rlv01

##### Well-known Member
I'm glad it worked for you.

Replies
5
Views
1K
Replies
11
Views
174
Replies
5
Views
160
Replies
1
Views
89
Replies
3
Views
83

1,141,062
Messages
5,704,055
Members
421,325
Latest member
tapete86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back