Need help with writing variant back to sheet

B

bri0710

I have some code that makes use of a variant to transfer a column of
data on one worksheet to a diagonal set of cells on another sheet.

I basically loop and assign the column data to the diagonals to the
variant, then equate the variant back to the new sheet (as a whole
block).

Here's my issue: I have formulas on the new sheet that I would like to
retain (that are on the off diagonals), but when I assign the variant to
the new sheet (since it's the complete block), it's overwriting the
formulas.

Is there a way to put the values on the sheet and retain the formulas?
Maybe creating the variant as a diagonal range in the first place? I
wasnt having much luck with that.

Thanks for the help!
 
A

Alan Beban

bri0710 said:
I have some code that makes use of a variant to transfer a column of
data on one worksheet to a diagonal set of cells on another sheet.

I basically loop and assign the column data to the diagonals to the
variant, then equate the variant back to the new sheet (as a whole
block).

Here's my issue: I have formulas on the new sheet that I would like to
retain (that are on the off diagonals), but when I assign the variant to
the new sheet (since it's the complete block), it's overwriting the
formulas.

Is there a way to put the values on the sheet and retain the formulas?
Maybe creating the variant as a diagonal range in the first place? I
wasnt having much luck with that.

Thanks for the help!
Click to expand...
Sub Test12345()
Set rng1 = Sheets(10).Range("A1:A10")
Set rng = Sheets(9).Range("A1")
For i = 1 To 10
Set rng = Union(rng, Range("a1")(i, i))
Next
k = 1
For Each iCell In rng
iCell.Value = rng1(k)
k = k + 1
Next
End Sub

Alan Beban
 
T

Tom Ogilvy

a refinement might be:

Sub Test12345()
Dim rng as Range rng1 as Rang, i as Long
Set rng1 = Sheets(10).Range("A1:A10")
Set rng = Sheets(9).Range("A1")
For i = 1 To 10
rng(i,i).Value = rng1(i).Value
Next
End Sub

Also doesn't require Sheet(9) to be the active sheet.
 
B

bri0710

Thanks for the help!

I was hoping to use variants for the speed benefit, but this will work.
I'm stuck with a slower PC, and just doing 200 cells takes 60 seconds.
 
T

Tom Ogilvy

You can't lay down a 2D array and only overwrite some of the cells in the
target. You could pick up the source area, pick up the destination area,
update the array for the destination area, then put it back down.

Sub Test12345()
Dim rng As Range, rng1 As Range, i As Long
Dim varr as Variant, varr1 as Variant
Set rng1 = Sheets(2).Range("A1:A10")
Set rng = Sheets(3).Range("A1").Resize(10, 10)
varr1 = rng1.Value
varr = rng.Formula
For i = 1 To 10
varr(i, i) = varr1(i, 1)
Next
rng.Value = varr
End Sub
 
A

Alan Beban

bri0710 said:
Thanks for the help!

I was hoping to use variants for the speed benefit, but this will work.
I'm stuck with a slower PC, and just doing 200 cells takes 60 seconds.
Click to expand...
Could you first transfer the entire block of formulas to the array, then
load the diagonal of the array, then transfer the whole array back to
the sheet?

Alan Beban
 
B

bri0710

That's a good idea actually.

I tried to assign the array to the range with the .Formula tag, but I'
getting the "Can't assign to array" error when it gets to that line.

Is there a better way (aside from using a loop) to assign a shee
filled with formulas to an variant array?


Thank
 
Top