0

I would like to import a range as a variant with some slight changes to what is stored in the variant, and then export the [modified] variant to a worksheet as a new range, that is then reread back in as a new range where I can do a new set of calculations using excel's built in functions by rereading the range into a new variant

A method describing exports a range to a worksheet can be found here

http://www.cpearson.com/excel/SortingArrays.aspx

Under

Sorting Arrays in VBA.

The reason I want to do this is so I can gain access to excel's functions over the data vs having to recode the functions capability in order to use it on an array or variant.

Community
  • 1
  • 1
thistleknot
  • 1,098
  • 16
  • 38
  • sorry, the last part of your question is not clear - are you asking how to pass a range reference into a function? – Tim Jul 22 '14 at 05:34
  • this is a followup question to an original issue here http://stackoverflow.com/questions/24877205/vba-run-worksheetfunction-on-range-derived-variant-array. I know how to pass a range reference into a function. What I want to know is how to pass a range into a new worksheet. – thistleknot Jul 22 '14 at 05:36
  • I don't understand too, if you have to write the elements of your array in a new Worksheet you can use single commands or a loop. Else if you need to use excel's built-in function, you can use the WorksheetFunctions... Can you make your question more clear? – Noldor130884 Jul 22 '14 at 05:43
  • "if you have to write the elements of your array in a new Worksheet you can use single commands or a loop." Please tell me how to do this, and immediately reload the data that was exported to a new worksheet as a newly loaded range (just as is done with the cpearson example). – thistleknot Jul 22 '14 at 07:40
  • "if you have to write the elements of your array in a new Worksheet you can use single commands or a loop." In the cpearson example provided. Their is data that is held in the original ranged data that is desired to have a "sort" function ran on it. The only way "sort" can be ran is to export the data to a worksheet, then run sort. I have a similar issue where I need to export data that is held as a variant to a worksheet, then reload the data as a range, so I can gain access to the sort function. I need to do this so I can do worksheetfunctions on variant held data. – thistleknot Jul 22 '14 at 07:47
  • this has a nice clear example or copying an array into a range without looping: http://www.xtremevbtalk.com/showthread.php?t=157783 obviously you can also return the 'Value' property of a range into a variable using the reverse syntax – Tim Jul 22 '14 at 08:09

0 Answers0