# 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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### 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

#### 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
1
Views
50
Replies
5
Views
1K
Replies
11
Views
208
Replies
5
Views
196
Replies
1
Views
107

### Forum statistics

1,143,837
Messages
5,721,088
Members
422,339
Latest member
SHIVATVM ### 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