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

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
Joined
May 16, 2017
Messages
1,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Solution

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,062
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I'm glad it worked for you.
 

Forum statistics

Threads
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.
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
Top