CODEWITHSUNDEEP
excelvbarangecells
sinDizzy
sinDizzy

Reputation: 1364

Split an Excel range into single cells

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?

Upvotes: 0

Views: 4277

Answers (2)

sinDizzy
sinDizzy

Reputation: 1364

It ended up being too complex and really not do-able. I wanted to write a function that would populate several cells. Instead I switched over to just writing a subroutine that would iterate through all the rows and copy/pase the range that was matched. Thanks all for the help.

Upvotes: 0

matzone
matzone

Reputation: 5719

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

Upvotes: 1

Related Questions