Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not able to copy a VBA array with formulas into an Excel range
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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200903/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not able to copy a VBA array with formulas into an Excel range
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. "musicgold via OfficeKB.com" wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200903/1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not able to copy a VBA array with formulas into an Excel range
Dave,
Thanks. You are right; the formula was wrong. How silly of me! MG Dave Peterson wrote: 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.a...mming/200903/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200903/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy array formulas | Excel Discussion (Misc queries) | |||
Formulas assignment from array to range in VSTO Excel doesn't work | Excel Worksheet Functions | |||
Excel automation: Problem with inserting an array of formulas into a range | Excel Programming | |||
copy one array formula to an array range | Excel Programming | |||
Excel copy formulas using non contiguous range | Excel Discussion (Misc queries) |