Not able to copy a VBA array with formulas into an Excel range

[musicgold via OfficeKB.com]

Hi,

I am trying to transfer the contents of an array into a range of Excel (red
line in the code below). Though I am able to do that when the array has
values, however, it is giving me an error when the array has formulas.
How can I get this to work?

VBA Code:
...
Dim Vma As Variant
...
TotalRows = .Range("Spread").Count

Vma = Application.Transpose(.Range("Spread"))


For i = TotalRows To 1 Step -1

Vma(i) = "=RC[-2]- RC[-1])"

Next

.Range("Spread") = Application.WorksheetFunction.Transpose(Vma)
...



Thanks,

MG.

 

[Dave Peterson]

Your formula isn't valid (the ()'s don't match):

Vma(i) = "=RC[-2]- RC[-1])"
should be:
Vma(i) = "=(RC[-2]- RC[-1]"
or
Vma(i) = "=(RC[-2]- RC[-1])"

I'm not sure if this is a simplified version, but how about:

with worksheets("somesheetnamehere")
.range("Spread").formular1c1 = "=RC[-2]- RC[-1]"
end with

ps. I'd be explicit with the .formular1c1 in your code, too.

Hi,

I am trying to transfer the contents of an array into a range of Excel (red
line in the code below). Though I am able to do that when the array has
values, however, it is giving me an error when the array has formulas.
How can I get this to work?

VBA Code:
...
Dim Vma As Variant
...
TotalRows = .Range("Spread").Count

Vma = Application.Transpose(.Range("Spread"))


For i = TotalRows To 1 Step -1

Vma(i) = "=RC[-2]- RC[-1])"

Next

.Range("Spread") = Application.WorksheetFunction.Transpose(Vma)
...

Thanks,

MG.

 

[musicgold via OfficeKB.com]

Dave,

Thanks. You are right; the formula was wrong. How silly of me!

MG

Your formula isn't valid (the ()'s don't match):

Vma(i) = "=RC[-2]- RC[-1])"
should be:
Vma(i) = "=(RC[-2]- RC[-1]"
or
Vma(i) = "=(RC[-2]- RC[-1])"

I'm not sure if this is a simplified version, but how about:

with worksheets("somesheetnamehere")
.range("Spread").formular1c1 = "=RC[-2]- RC[-1]"
end with

ps. I'd be explicit with the .formular1c1 in your code, too.

Hi,

[quoted text clipped - 27 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200903/1