Getting list from range in formula

aalansari

New Member
Joined
Oct 9, 2013
Messages
17
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top