Variant Array Of Arrays Of Differing Size

Jan 24, 2013

I am trying to develop some code to serve as an 'undo' for several macros that I have. They each take a selected range and perform some changes to that range. I have managed to make some code that will undo the last macro run but would like to make something that can go back several steps. To that end, I have started with the following code but am running into an issue when I need to use a variant array to hold multiple arrays of a custom defined data type:

VB:
Type SaveRange
Val As Variant
Addr As String

[Code] .....

This declares some public variables I'll need, each as an array so that I can iterate through several steps of do/undo using the undoIndex. I then use them to save each range I am about to change by calling the following macro within my actual data-manipulating macros:

VB:
Sub Save_RangeForUndo(rng As Range)
Dim i As Integer
Dim cell As Range

undoIndex = undoIndex + 1

[Code] .....

If you look at the last line here, this is my problem; I can't figure out how to properly use an array to hold each instance of OldSelection() for later referral. Just for completeness, here is my actual undo macro:

VB:
Sub Undo_Operation()
Dim i As Integer
OldWorkbooks(undoIndex).Activate
OldWorksheets(undoIndex).Activate

[Code] .....

I need to get this OldSlctVariant()() array to hold each instance of OldSelection() so that I can restore them for each consecutive undo. I'm not very familiar with the variant type and anything more than the basic use of arrays.

View 7 Replies


ADVERTISEMENT

How Many Elements Are Not Empty Within Variant Array

Jul 19, 2014

I want to test how many elements are not empty within a variant array.

For example for arrays Arr1 and Arr2 below the answers would be:
- for Arr1 not empty elements=1
- for Arr2 not empty elements=0

Arr1(0)=empty
Arr1(1)=2
Arr1(2)=empty

Arr2(0)=empty
Arr2(1)=empty
Arr2(2)=empty

I've tried with function COUNTA inside VBA but counts even the empty values:

Code:
NotEmptyCells = Application.WorksheetFunction.CountA(Arr1)

Is there another function to count this from an array or alternative way?

View 9 Replies View Related

Why Doesn't My Variant Array Work

Jun 26, 2008

The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time,

The code I have is:

Sub test()
Dim vSheetColours As Variant
Dim iCounter As Integer
vSheetColours = Range("Colours").Interior.ColorIndex
For iCounter = 1 To UBound(vSheetColours, 1)
MsgBox vSheetColours(iCounter, 1)
Next iCounter
End Sub

(Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!)

Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours.

If I run this code, I get a runtime error 13 type mismatch, and it highlights
For iCounter = 1 To UBound(vSheetColours, 1)

But if I replace
vSheetColours = Range("Colours").Interior.ColorIndex
with
vSheetColours = Range("Colours").Value

it works fine.

View 9 Replies View Related

Advance Variant Array Without Loop

Jul 13, 2006

Is it possible to use an array without looping through it? I have code that has two loops: One that is just for the array and the other that loops through the data. It would be nice if I could get it down to one loop, but I'm not sure if it's possible. For example, if you have
Dim varArea As Variant

varArea = Array(1,2,4,6,8,12)

For x = LBound(varArea) To UBound(VarArea)
Do While Not C Is Nothing
Code here
Is there a way to advance to the next item in the array without using the loop.

View 10 Replies View Related

Convert Variant To Typed Array

May 23, 2007

I am using strongly typed code, but I am also pulling data from worksheets into variants. e.g.

Dim MyArray As Variant

MyArray = [MyNamedRange]

is there any shortcut to convert the variant to a typed array, which doesn't just involve looping throught the variant and using a casting function, e.g.

Dim MyArray As Variant
Dim typedArray() As type

MyArray = [MyNamedRange]
typedArray = shortcut(MyArray)

View 4 Replies View Related

Combining Two Arrays (unequal Size)

Sep 28, 2007

I am using this formula

=OFFSET(Inputs!$E$124,0,0,COUNTA(E124:E168),1)

to get an array with the names of some inputs excluding the blanks ... hit F9 to see that it works fine...

Now i want to combine the arrays produced by this formula for two different lists.

how to achieve that? please let me know if this is too vague..

View 9 Replies View Related

Populate 2D Array From Other 2D Arrays

Jul 21, 2014

I have read in 2 blocks of data as 2D arrays, and wish to create a third 2D array from some of the elements of the first two. When I do this I get an output range that is the correct size, however each row of data in the range is identical. This is what I am using:

[Code] .....

So my main concern is how to get each row of data being printed to Sheet2 to be the correct rows, and not just one row repeated thousands of times.

View 1 Replies View Related

Combine Arrays Into One Array

Apr 29, 2007

an array formula to combine the array formulas in columns B, C, and D? I would like the results displayed in one cell....

View 9 Replies View Related

Use SUM Function With Two Arrays / One Array To Be Negative Value?

May 12, 2014

I am trying to subtract two values, which are calculated from arrays. Here is my actual code:

[Code] ......

If I remove the "-", I get the sum of both arrays as expected. If I include the "-", I get the "#VALUE!" error message.

View 4 Replies View Related

Redim Array Function With Pre-Dimensioned Arrays?

Apr 1, 2014

I've written this function to re-dimension an array based on the size of the range it is storing. Originally all my variables were simply declared as:

Code:

Dim XLRecOutput() as Variant

I then wrote a function which took the variable and the range as follows:

Code:

Function LoadRangeToArray(dataArray() As Variant, selectedRange As Range, Optional blnLoadData As Boolean = True)
With selectedRange
ReDim dataArray(1 To .Rows.Count, 1 To .Columns.Count)
End With
If blnLoadData Then dataArray = selectedRange
LoadRangeToArray = dataArray
End Function

To call the function, it's simply:

Code:
XLRecOutput = LoadRangeToArray(XLRecOutput, Range("XLRecOutput"))

Now I've created a class and set up Get/Let properties for the variables instead, but the above function fails with a Compile error on the call - 'Type mismatch: array or user-defined type expected' and I can't get my head around how to resolve it.

View 9 Replies View Related

Sum Of Products With Non Parallel Arrays With 1 Array In Reverse Order

May 31, 2014

I need to calculate a Sums of Products with unusual requirements. Please refer to the attached picture. The Orange Cells under "Noise Weight" are the Formula Cells. The Values in those Cells need to evaluate as shown in the equations below. I want this to be dynamic, so that if I expand the FM Region (Add Columns between "Noise Weight" and "FM15") AND expand the Severity Region (Add Rows between Severity and Noise Weight), I will not have to update Formulas manually. I know for sure that the standard SUMPRODUCT Function cannot handle this.

PRODUCT 1 = D29*E31 + D28*F31+D27*G31.....+D15*S31
PRODUCT 2 = D29*E32 + D28*F32+D27*G32.....+D15*S32
PRODUCT 3 = D29*E33 + D28*F33+D27*G33.....+D15*S33
...
....
PRODUCT 7 = D29*E37 + D28*F37+D27*G37.....+D15*S37

View 11 Replies View Related

Summing The Multiplication Of One Array Times Multiple Arrays

Jul 24, 2008

I am trying to multiply one array of prices for multiple input products across
the volume that that product my go into multiple end products.

I know you can use SUMPRODUCT with two arrays that are equal BUT I want to Multiply the price matrix across 15 or 16 other matrices and sum all the products.
The price matrix and the other arrays are all 1 x "whatever".

View 9 Replies View Related

Array Size Blowing Up

Feb 19, 2008

I have a named range that is 993 cells in length (columns). I verified this in the name manager. In my VBA code I am getting a range error when I try to access element number 903. 903 should be the last one with any data in it. I can't see a reason why this would generate an error since i have not exceeded my size of 993.

Is there a VBA function or property that I can use to determine the size of an array?

View 9 Replies View Related

Size Of Array - Ubound

Mar 23, 2007

How to calculate the size of an input variant using UBound? Sometimes I need to do the transpose but sometimes I don't. I tried ON ERROR GOTO but it doesn't work in this case. Can you tell me how I could do that?

Public Function Shock(A_E As Variant, Year As Variant) As Double

Dim n As Integer

n = UBound(A_E,1)

Shock = n

End Function

View 9 Replies View Related

Declare An Array With Variable Size

May 7, 2009

I need to create an array with a variable as it size For instance:

View 2 Replies View Related

Setting Size Of Array Using Cells (VBA)

Dec 12, 2013

I'm trying to set a range variable ('IngFore') as equal to a number of rows long and a number of columns wide via 'Cells'. The range needs to be dynamic in that the total number of rows and columns will vary over time. Hence, I've defined 'lastdate' to determine the last row in the array, and 'lastcol' to determine the last column in the array. The starting point for the array is I12.

It would appear that I can't quite get the syntax right.

Code:
Set IngFore = (Cells(12,9 to lastdate,9)),(Cells(12, 9 to 12, lastcol))

I've tinkered with it here and there, removing and adding brackets but I can't get it to work.

View 3 Replies View Related

Randomize & Size Dynamic Array

May 16, 2008

I play in a rock band and i'm trying to create a randomly generated set list based on categories.

Rock, Blues, Slow, & Original are the categories

My sheet of songs is sorted perfectly by these categories already and I have dynamic ranges already named to their respective categories.

the module i am using to randomize these categories does not dynamically expand....

View 8 Replies View Related

Loading An Array With A Varying Range Size

Mar 12, 2009

I want to load one with data from a worksheet query table that will vary in size each time it's executed. The length will vary, but the range is continuous, and five columns wide. I'd prefer a do while loop, looping until a blank cell is encountered. Can someone get me started with general syntax and setup of the array?

View 9 Replies View Related

Display Array In Variable Size Userform

Jan 23, 2008

I am trying to figure out how to make a userform to display the contents of a 2-D array which has a variable number of rows. I want the userform to height of the userform to correspond with the number of rows of data to display.

I don't have much experience with userforms, but here's what I was thinking:
VBA code which would find the # of rows of data and then adjust the height of the userform and the length of the lable (which the data would go in).

View 4 Replies View Related

Type Mismatch When Transposing 65556 Size Array Into Range

Feb 22, 2014

I was looking for a worksheet with vba to do FFT beyond the Excel internal 4096 limit. I wanted to try my hand at writing code to do it faster. The attached does just that.

Everything worked fine until I went beyond 32k samples. The next higher 2^n value for data size to feed the FFT is 65556. When I tried this quantity of samples I got Runtime Error (13) type mismatch when attempting to write the array back to the worksheet using the "transpose" method.If I remember correctly, 32k worked.

Is there some limit to the size of the array that can be transposed and placed into a range? If so, is there another way to do this?

I do not need to re-write the data, but I use this same method elsewhere to write the FFT output. It it fails writing the input data back, it will probably fail when writing the FFT results.

Attached File : FFT.xlsm‎

View 1 Replies View Related

Slicing And Dicing CSV Files - Involves Arrays And Jagged Arrays

May 8, 2013

I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:

|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|

.
. extending until the end of the data set
.

I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:

Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub

View 1 Replies View Related

How To Use MAX Function With Differing Datasets

Aug 7, 2013

I'm trying to work out a way to use a MAX function and IF Function to determine how i can find the highest number since a trade began. For example, i have a countif function that is identifying each trade (Column B) so what i want to do is to find the highest number for each trade (Column A) which is represented by dummy variable 1,2,3,4 etc. Please see example below: what i want to do is write code which runs for 3500 lines and finds the highest price since trade began and trade's are represented by 1,2,3,4,5,6,7 etc.

Close
Number
TRADE PRICE
HIGH PRICE

0.6347
1
$0.63
0.6565

0.6373
1
$0.63

[Code] ........

View 7 Replies View Related

Fill Between Two Values But A Differing Column.

Dec 21, 2009

I want the macro to go to column F and copy down from the row containing AO NAMES down to the row containing TOTAL with the value within cell C3.

So basically AO NAMES will be the starting point and TOTAL will be the end point for a value to be copied down in a different column.

Example: Value in C3 = EU11

Column B Column F
AO NAME EU11
Chris Wright 1355 EU11
Hazel Thomas 4015EU11
Julie Cunningham 8426EU11
Dawn Cumming 8748EU11
Jacqui Connolly 16597EU11
David Newton 17978EU11
Duncan Cowen 25781EU11
0EU11
0EU11
0EU11
0EU11
0EU11
TotalEU11

View 5 Replies View Related

Multiple Criteria From Differing Rows

Mar 18, 2007

I need to return a value if multiple criterion are met from different rows. I tried an if and formule but will not get a value since information is not entered on the same row. My criteria is text and numbers.

View 9 Replies View Related

Find Most Common Sum From Table Of Differing Values?

Apr 24, 2013

Problem description: I need to put together twelve arrows each comprised of four components; field point, front insert, shaft & fletch and nock. Therefore, I have forty-eight components in an Excel table. Each of these components may differ slightly in weight. My objective is to mix and match all of the components to create as many arrows as possible of the same exact weight.

Two questions. 1) How should I be thinking about setting up the problem? 2) Which Excel function returns the results I'm looking for?

Total weight
Point
Insert
Nock
Shaft

?
100.1
109.7
20
179.1

100.1
110

View 5 Replies View Related

Count Of Multiple Values With Differing Weightings

Nov 30, 2007

Dave H kindly provided the formula below to sum all cells in a range with multiple values: =SUM(COUNTIF(D3:Y3,"a*"),COUNTIF(D3:Y3,"b"),COUNTIF(G2:Y2,"c"))

however my supervisor has just informed me that any A,B or C that appears in the J column is worth 4 instead of 1. Very annoying not to be given all the facts beforehand

so

J3...K3...L3
A....C....B

will equal 6 instead of 3

View 2 Replies View Related

Matching Stock Portfolios With Differing Company Names

Jan 8, 2012

I'm trying to match stock portfolios of mutual funds with stock indexes to find out how much the portfolios deviates from the indexes (active share).

The deviation is simply calculated as the percentage of the portfolio holdings not included in the index.

The data I have is 10 years of monthly portfolio weightings for 30 mutual funds and 8 indexes, organized with company names in column A and the percentage weight for each company in each month in the adjacent rows (see simplified screenshot). I want to match all companies with all indexes.

Twofold:

1)The mutual fund data comes from one source and index data from another. This means company names are not spelled exactly the same.
2)Within the mutual fund datasets, a company might be listed more than once with different names. The different names refer to different periods in time, so what should be matched with the index is the sum of these columns.

The specific task that I need to accomplish is to match company name in the portfolio with the index and then return the absolute difference between weight in the portfolio and the weight in the index for each month. (see screenshot).

Hence the result I'm looking for is a matrix of company names (of each portfolio) in col A and the absolute difference between the portfolio and the index weight in the adjacent row.

The best solution I've come up with is to device a list of all the portfolio company names, including "doubles", and then for each index, add these company names and copy the row weightings. This I'm not particularly happy with because index weightings would no longer sum to 100 and it would be difficult to check for errors. Also, it would still take a lot of time.

View 6 Replies View Related

Set File Extention As Variant

Jul 1, 2009

I have a button the runs Application.GetOpenFilename And puts the output filepath into a label. I then have another button that opens a inputbox asking to change the file name and then copys the file from above path to my destenation.

when setting the destination i use ThisWorkbook.Path & Application.PathSeparator & "Images" & Application.PathSeparator and the imputbox value as the filename. doing it like this it wont alocate a extention for the file ".jpg,ect". The label.caption has the .ext and i would like to be able to store everythink after the . as a variant.

View 2 Replies View Related

Errors In Using Variant Variable

May 7, 2006

I experienced error "object required" when I tried to pass a argument of type variant into the b/m function call.

Call closeCon(con) 'con is of type Variant

The function is as follows.

Public Sub closeCon(ByVal nwAdoCon As ADODB.Connection)
nwAdoCon.Close
End Sub

View 9 Replies View Related

Converting A Variant Into A String

May 17, 2006

Is there any way in VBA to convert a Variant into a String? This code doesn't seem to be valid:

Dim vName As Variant
Dim sName As String

vName = "John"
sName = CStr(vName)

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved