Getting list from range in formula

[aalansari]

Example: To calculate the sum of values in the range of A1:A10 I can use

=SUM(A1:A8)

or I can use

=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.

=SUM(A1:A10)/COUNT(A1:A5)

)
2. It breaks when I use paranthesies before my formula (e.g.

=(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:

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

 

[rlv01]

A different approach. This works for all 3 of your examples.

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]

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

 

[rlv01]

I'm glad it worked for you.