split one dimensional array and write out to multiple rows

quarna

New Member
Joined
Oct 25, 2021
Messages
38
Office Version
  1. 365
Platform
  1. Windows
Hello

The code below writes a range("M5:V44") to another sheet in one row.
I have trouble getting the data back to the same range as it came from.

VBA Code:
Sub writeRange()

    r = 1  ' row with ID
    k = 46  ' column in the destination sheet
    id = shKerneOplysninger.Range("C4").Value
   
    Dim rg As Range
    Set rg = shKerneOplysninger.Range("M5:V44")
    Dim arr As Variant
    arr = rg.Value
   
    Do While shKerneData.Cells(r, 1).Value <> ""
        If shKerneData.Cells(r, 1).Value = id Then
            For i = LBound(arr) To UBound(arr)
                For j = LBound(arr, 2) To UBound(arr, 2)
                    shKerneData.Cells(r, k) = arr(i, j)
                    k = k + 1
                Next j
            Next i
      End If
      r = r + 1
    Loop
End Sub

----------------------------

I can get the values back in an array and they are always in column 46-445.
But in the array they are stored as arr(1,1) to arr (1,400) and i belive that is one dimensional.

How can i write them out to range ("M5:V44") "it's 10 columns and 40 rows"

VBA Code:
Sub getRange()
   
    r = 1  ' Row with ID
    id = shKerneOplysninger.Range("C4").Value ' Prøvenummer
   
    Dim arr As Variant
    Dim rg As Range
   
    Do While shKerneData.Cells(r, 1).Value <> ""
        If shKerneData.Cells(r, 1).Value = id Then
            Set rg = shKerneData.Range(Cells(r, 46), Cells(r, 445))
            arr = rg.Value
           
        End If
   
    r = r + 1
    Loop
   
   

End Sub
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
A few comments.

Typically when there is a statement like arr = rg.Value to read a range into an array, there is also somewhere else a statement like rg.Value = arr to write the array values back to the range. I don't see that in your code. How & where do you write the array data back to the range?​

In Sub writeRange(), this statement arr = rg.Value will produce a 40 x 10 array for range "M5:V44".​

But in Sub getRange() the same statement arr = rg.Value will produce a 1 x 400 one dimensional array, which cannot be written to a 40 row x 10 col range of cells​

You need some sort of strategy to produce an array of the right size to write back to range "M5:V44"
 
Upvote 0
A few comments.

Typically when there is a statement like arr = rg.Value to read a range into an array, there is also somewhere else a statement like rg.Value = arr to write the array values back to the range. I don't see that in your code. How & where do you write the array data back to the range?​

In Sub writeRange(), this statement arr = rg.Value will produce a 40 x 10 array for range "M5:V44".​

But in Sub getRange() the same statement arr = rg.Value will produce a 1 x 400 one dimensional array, which cannot be written to a 40 row x 10 col range of cells​

You need some sort of strategy to produce an array of the right size to write back to range "M5:V44"

Ohh..
Made sense and i found a solution. :)


VBA Code:
Sub getRange()
  
    r = 1
    id = shKerneOplysninger.Range("C4").Value
  
    Dim arr As Variant
  
    Dim rg As Range
    Set rg = shKerneOplysninger.Range("M5:V44")
  
    Do While shKerneData.Cells(r, 1).Value <> ""
        If shKerneData.Cells(r, 1).Value = id Then
            arr = shKerneData.Range(Cells(r, 46), Cells(r, 445))
            rg.Value = arr
        End If
    r = r + 1
    Loop
  
End Sub

Nice feeling to have accomplised it myself.. thank you
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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