Split an Excel range into single cells

Question

In Excel 2010 I have a VBA function which searches a worksheet and finds a match based on three values. Once it finds the match it returns a range which is an entire row of data. Now in my Excel worksheet in one cell I call that function. Using a formula can I split that range into individual cells. It would basically be like find the row and then copy paste the cells starting at the calling cell. The only thing I can think of is to save the range in a cell

A1 = {my range}

and then each cell after that use an INDEX function like

A2 = INDEX(A1, 1,1)
A3 = INDEX(A1, 1,2)
A4 = INDEX(A1, 1,3)

Looking for any suggestions. I could have sworn there was a non-VBA function that I could use that would split out a range into individual cells.

Edit with an example: In my main worksheet I have tons of data and some of it has errors. Those with errors I look up in another worksheet.

main worksheet
status, height, width, weight, sheet, m, d, z, e
ok, 1, 2, 5, , 100, 5, 7, 5
err, 1, 2, 6, TEMPA 
err, 1, 2, 7, TEMPA
ok, 1, 2, 8, , 102, 7, 9, 8
ok, 1, 2, 9, , 104, 8, 7, 0

the combo of height, width, and weight are unique. the helper woksheets are the same as the main but will have the data that is missing.

TEMPA
ok, 1, 2, 6, , 25, 66, 0, 1
ok, 1, 2, 7, , 99, 7, 2, 6

So in essence I want to join the two on height, width, and weight but only for those that have an error. The name of the helper worksheet will vary but that is pre-calculated. I have the code written to take the three unique conditions, go to the helper worksheet, make a match and then return the range object consisting of the matching row.

function GetMyRange(h, wd, wt, sht) as Range
   'do the match here
end function

The function works as I can setup some debug statements and I'm making the correct matches. However back in the main worksheet how do i take my range object and put the individual elements of the range object into each cell to "fix" the errors?

Are you trying to replace it from returning an entire row in one cell? Or did you still want it to do that? — user2140261
I want to return the entire row but place the cells from the source into my target but in individual cells. Just like a copy/paste. — sinDizzy
You'll need to use a subroutine to do this, a worksheet function or a UDF called from the worksheet cannot do this sort of thing. — David Zemens
so right in the VBA function iterate through each source cell and assign that to new target cells? that's what i was thinking of doing but how do I reference the cell in which the function is called? — sinDizzy
You can enter your function using an array formula, and it will populate the cells in which you've entered the formula. Helps to know in advance how many cells you'll need... Look up array formulas for more details on this approach. Great reference here: cpearson.com/excel/arrayformulas.aspx — Tim Williams

matzone matzone · Accepted Answer · 2013-06-21T16:49:56

Not too clear about your case but I have this ..

This will give example to extract multi range to single range ..

Sub SplitRange()
Dim rn As Range
Dim c As Range

Set rn = Range("A1:A8")
For Each c In rn.Cells
    MsgBox StrRange(c.Row, c.Column)
Next
End Sub

Function StrRange(ByVal nRow As Single, ByVal nCol As Single) As String
Dim sC As String
Dim nC, nRest, nDivRes As Integer

sC = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
nC = Len(sC)

nRest = nCol Mod nC
nDivRes = (nCol - nRest) / nC

If nDivRes > 0 Then StrRange = Mid(sC, nDivRes, 1)
StrRange = StrRange & Mid(sC, nRest, 1) & Format(nRow)
End Function

Hope this help and everyone that need something like this ..

Split an Excel range into single cells

2 Answers