VBA Copy Cell Values for different ranges?

[elkinm]

Hi, I have some macros to copy range values to other locations, but the ranges are dynamic and sometimes I need to do an operation on the data.

Lets say I need to copy Range("A1:A10") to Range("C1:C20"). (notice the destination range is larger).

So I have:

myArray = Range("A1:A10")

Loop for operation on array.

Range("B1:B20") = myArray.

This works but the Range("C11:C20🙂 becomes populated with N/A causing errors and possibly delegating data.

If I copy paste in excel, then only range C1:C10 is affected.

Is there a way to have VBA not cause errors when pasting to larger ranges?

Thanks

 

[wetech]

might be a silly question, but what exactly do you expect to get copied into the extra cells of the destination range?

 

[elkinm]

I expect the extra ranges to be untouched.

If I select range A1:A10 in excel and copy, then select C1:C20 and paste C1:C10 is replaced with what was in A1:A10 cells C11:C20 remain unchanged. This is what I want it to work with VBA.

If this cannot be done easily, is there a way to only copy so many cells from an Array or range.

Say I calculate size of the A1:A10 range, which is 10 as a result I should only copy the first 10 cells of the array of look at the C1:C20 range see that I only have 10 cells, make the destination range C1:C10.

I suppose I could use Cells instead of Range or generate the Range as "C1:C" + Ubound(array), it just seems overly complicated for seething Excel can do on it's own.

On a slightly different note, is it possible to choose which values of an array or range get copied. Meaning if I have an array for length 20 and a destination range of A1:A10. Instead of placing values 1 through 10 to A1:A10 is it possible to copy values 5 to 15 to the destination range?

Thanks again.

 

[wetech]

I expect the extra ranges to be untouched.

If I select range A1:A10 in excel and copy, then select C1:C20 and paste C1:C10 is replaced with what was in A1:A10 cells C11:C20 remain unchanged. This is what I want it to work with VBA.

If this cannot be done easily, is there a way to only copy so many cells from an Array or range.

Say I calculate size of the A1:A10 range, which is 10 as a result I should only copy the first 10 cells of the array of look at the C1:C20 range see that I only have 10 cells, make the destination range C1:C10.

this will work.

I suppose I could use Cells instead of Range or generate the Range as "C1:C" + Ubound(array), it just seems overly complicated for seething Excel can do on it's own.

excel can actually be a bit tricky with copy and pasting to a different sized range of cells. If the size of destination range is equally divisible by the size of the copied range, it will duplicate the data to fill the destination. If it isn't equally divisible, it will paste it once.

for example, let's say cells A1 and A2 look like this:
1
2

if you copy and paste this into cells B1:B4, you'll get:
1
2
1
2

but if you paste into cells B1:B5 you'll get:
1
2

On a slightly different note, is it possible to choose which values of an array or range get copied. Meaning if I have an array for length 20 and a destination range of A1:A10. Instead of placing values 1 through 10 to A1:A10 is it possible to copy values 5 to 15 to the destination range?

Thanks again.

loop through the cells of the destination range using a counter and set the values based on the value in the array:

assuming your array is called a()

for i = 1 to range("A1:A10").cells.count
range("A1:A10").cells(i,0).value = a(i+4)
next i

 

[elkinm]

Thanks, I think for simplicity and possibly speed, I would probably create a second array, extract values that I need from array1 and then copy it to the range.

I have run into another annoying issue.

I do:

TestRange = Sheet3.Range("A1")

Then I do:

TestRange = 5.

This code runs and when it runs it ways TestRange has a value of 5, but nothing is copied into the cell Sheet3.Range("A1")

I tried doing TestRange.Address just to see where it is copying, but that gives me error 424 object required.

If I declare use Dim TestRange as Range then when I do TestRange = Sheet3.Range("A1") I get error 91 Object Variable not set.

I just want to set the variable to a fixed range so that I can reference it at any time, even with different workbooks and find the same cells.

This seems so simple and I just don't understand why it does not work.

Thanks again.

 

[wetech]

Thanks, I think for simplicity and possibly speed, I would probably create a second array, extract values that I need from array1 and then copy it to the range.

I have run into another annoying issue.

I do:

TestRange = Sheet3.Range("A1")

Then I do:

TestRange = 5.

This code runs and when it runs it ways TestRange has a value of 5, but nothing is copied into the cell Sheet3.Range("A1")

I tried doing TestRange.Address just to see where it is copying, but that gives me error 424 object required.

If I declare use Dim TestRange as Range then when I do TestRange = Sheet3.Range("A1") I get error 91 Object Variable not set.

I just want to set the variable to a fixed range so that I can reference it at any time, even with different workbooks and find the same cells.

This seems so simple and I just don't understand why it does not work.

Thanks again.

TestRange = Sheet3.Range("A1")

should be:
Set TestRange = Worksheets("Sheet3").Range("A1")

 

[elkinm]

Thanks again, that works. I definitely forgot the Set, just surprised I cannot use sheet3.

I did :

MyBook = ActiveWorkbook.Name
MySheet = Sheet3.Name

then

Set MyRange = Workbooks(MyBook).Sheets(MySheet).Range("A1")

Shouldn't Sheet3.Range("A1") be the same as Sheets("Sheet3").Range("A1")

I generally use sheet as it is never supposed to change whereas I can change the sheet name. I have only seen Sheet3 fail once before when for some reason all the sheets in a workbook were completely rearranged and everything went nuts.