Solved

How can Array be used to copy from a horizontal single-row range (50 contiguous cells) to non-contiguous vertical ranges of 10cells each?

Posted on 2011-03-08
3
308 Views
Last Modified: 2012-05-11
I'm trying to copy a static single-row horizontal range to five non-contiguous vertical static ranges.  They both have the same number of cells.

This is my code that doesn't work:
Range("$AV$76:$AV$85,$BA$76:$BA$85,$BG$76:$BG$85,$BM$76:$BM$85,$BS$76:$BS$85").value = Range("$AN$5:$CK$5").value

Thanks, --Andres
0
Comment
Question by:AndresHernando
3 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 50 total points
ID: 35068906
You can't do that. You will have to do it in 5 separate chunks.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 450 total points
ID: 35068965
Since the gap between the first two ranges is different from the gap between the remaining ranges you will have to use something like

Sub transnoncont()
Range("$AV$76:$AV$85").Value = WorksheetFunction.Transpose(Range("$AN$5:$AW$5"))
Range("$BA$76:$BA$85").Value = WorksheetFunction.Transpose(Range("$AX$5:$BG$5"))
Range("$BG$76:$BG$85").Value = WorksheetFunction.Transpose(Range("$BH$5:$BQ$5"))
Range("$BM$76:$BM$85").Value = WorksheetFunction.Transpose(Range("$BR$5:$CA$5"))
Range("$BS$76:$BS$85").Value = WorksheetFunction.Transpose(Range("$CB$5:$CK$5"))
End Sub
0
 

Author Closing Comment

by:AndresHernando
ID: 35069149
Thanks for the solution.  Works great.  --Andres
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Combine like values in a graph excel 2016 7 35
Stop display of alerts in Word via Excel 12 24
Getting rid of #VALUE! 7 23
Formula to run in whole column: 15 13
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question