Count Number Of Used Elements In Array Variable
Apr 4, 2009
array variables:
How can I count the number of elements of a particular dimension of an array variable that have actually been filled with items/values?
For example, the array variable in this procedure has two dimensions. Dimension 1 has three elements, and dimension 2 has 5 elements. I then add values to some, but not all the defined elements of dimensions. How can I count, for each dimension, the number of elements that have values rather than are empty?
Option Base 1
Sub test()
Dim ExampleArray(3, 5)
ExampleArray(1, 1) = 3
ExampleArray(1, 2) = 4
ExampleArray(1, 3) = 2
ExampleArray(1, 4) = 6
ExampleArray(2, 1) = 5
ExampleArray(2, 2) = 9
End Sub
View 9 Replies
ADVERTISEMENT
Sep 8, 2009
I was wondering if there is a way to have the elements of an array counted. I have something like this:
View 6 Replies
View Related
Apr 25, 2007
I have a workbook with several sheets, some are permanent and then there are certain sheets whose quantity varies depending on how many sub-contractors we use on a given job. The workbook starts as a template and there are 10 sub-contractor sheets to start with, if we only have 5 subs then the last five sub sheets are deleted. The sheets start out with the tabs named "Sub 1" "Sub 2" etc. but after the book is set up the sheet tab names are changed to the sub name. I've included some partial code below, with the goal being to run code on the sub-contractor sheets no matter the tab name or how many there are.
So the workbook has 5 "non-sub" related sheets, I was thinking that if I could do a sheet count -5, and get that number into the array it would accomplish what I want to do. And if someone added a new sheet it would still be processed with the others.
Public Sub SubConEstNum()
On Error Resume Next
Dim S As Worksheet, names As Variant, i As Integer
names = Array(6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
For i = LBound(names) To UBound(names)
Set S = Sheets(names(i))
S.Range("T3").Value = S.Range("S3").Value
S.Range("T1").Value = S.Range("S1").Value
S.Range("T2").Value = Sheets("Bill").Range("K1").Value
Next i
End Sub
View 3 Replies
View Related
Feb 11, 2008
I'm trying to use an array to carry out string function on a range of excel cells.
Here is the code I am using
Dim arrXl As Variant
arrXl = ws.Range("F1:F" & ws.Range("D1").End(xlDown).Row)
For i = LBound(arrXl) To UBound(arrXl)
MsgBox (LBound(arrXl))
'If 1st char is different from 3rd char then remove all of string after 1st char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
arrXl(i) = Left(arrXl(i), 1)
End If
'If 1st char is different from 7th char then delete string after 5th char
If Left(arrXl(i), 1) <> Mid(arrXl(i), 7, 1) Then
arrXl(i) = Left(arrXl(i), 5)
End If
The lbound function returns the value of 1 as the lower bound, I do not have "Option base 1" set so I was expecting the lbound value to be 0. The first 2 cells in the F column are blank so this may have something to do with it, I am unsure if cells in excel can be null if they can be null one cell may be null and the other may be a zero length string but I am unsure about this.
The ubound function returns a value of 487.
The code breaks when I try to access an element in the array so it breaks on the line:
If Left(arrXl(i), 1) <> Mid(arrXl(i), 3, 1) Then
and returns the "Subscript out of range" error message.
View 5 Replies
View Related
Mar 13, 2009
I have a report as below:
Cust IDCust NameAcct 1Acct 2Acct 3…Acct 10…Acct 100Total123AB 123 255 663 - - 1,041 456AC - 3 833 51,008 - 51,844 157AD - 46 46 550 - 642 226FE 5 7,733 - - 553 8,291 658FZ 9 8,833 - 444 555 9,841
and would like to get the final product as following:
Cust IDCust NameAcct 1Acct 2Acct 3…Acct 10…Acct 100Total123AB 123 255 663 - - 1,041 456AC - 3 833 51,008 - 51,844 157AD - 46 46 550 - 642 226FE 5 7,733 - - 553 8,291 658FZ 9 8,833 - 444 555 9,841 Total Region 1 137 16,870 1,542 - 52,002 - 1,108 71,659
Criterias:
# of Rows are variables.
# of Columns are variables.
Sum amount for each of account including total per customer ID.
Total region line is hilited and bold.
View 9 Replies
View Related
Dec 11, 2008
If I have say several hundred elements stored in an indexed array and I want to clear them each time the sub or function holding them is called, is there a way to erase the contents of the entire array without having to loop through each element.
Something like array().clear? Note that each time the sub is called, they are also redimed to some reference.
View 6 Replies
View Related
Dec 23, 2008
I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array. For example: Suppose i have 10 strings in the array. Is there a command to determine that there are only 10 elements in the 100 possilble array?
View 11 Replies
View Related
Apr 26, 2009
I can't seem to get my head around the simple issue of assigning a range of cell data to an array! Here is a snippet of what I have, maybe you can see the flaw:
View 8 Replies
View Related
Jan 21, 2012
I have a question regarding arrays. If I have too many elements in a 1D array(let's say 1000), how can I list all of them in a msg box (separated by comma)?
View 2 Replies
View Related
May 14, 2013
I am facing the some issue while tried to display the data from array. Below is code I have to tried to display the data from array.
Scenario: I have assigned the few values in my excel to an array and tried to display the data stored in array
Sub Main()
Dim Rows_Array() As Variant
Dim i As Integer,
Sheets("EmployeeWise").Select
Rows_Array = Range("C6:C" & Range("C65536").End(xlUp).Row).Value
For i = 1 To UBound(Rows_Array())
MsgBox Rows_Array(i)
Next
End Sub
View 1 Replies
View Related
Sep 1, 2007
I am trying to present a 10X10 Matrix Range (A1:J10) full with UNIQUE INTEGER random numbers. I thought to use an Single Dimension Array (100 deep) and fill it with the Rnd() Function.
Then, I thought to check the RANK of each element of that Array and transfer it to 100 cells (10X10) in the Sheet (assume A21:J30) with the help of 2 nested loops.
My problem is: How to find the Rank of each element within ARRAY1. Is there a way to refer to an Array as to a Range in a Worksheet. (I do not want to transfer 100 values from the Array to the Sheet - I rather prefer to check the Rank WITHIN(!) the Array).
Option Base 1
Sub MiKe()
Set AWF = Application.WorksheetFunction
H = 10
V = 10
Redim Array1(H * V)
For CL = 1 To H * V
Array1(CL) = Rnd()
Next
For HC = 1 To H
For VC = 1 To V.........................
View 2 Replies
View Related
Jun 30, 2014
Join an array with Text elements to create a string that can be Evaluated
So for instance if I have Array("A", "B", "C") and I want to evaluate("=({" & Join(array, ",") & "})="A)"). Is there any way to do this without having to loop or push to a Named array first? I'll even take this evaluate thing if I can do it with text and numbers
View 6 Replies
View Related
Mar 25, 2014
I've defined a string array and would want to use it as a basis for a vlookup. Is it possible to find the elements of the array directly in the worksheet ?So far I've got :
[Code] .....
View 2 Replies
View Related
Dec 3, 2009
Filling all elements of a two dimensional array?. I know that I could write something like:
View 3 Replies
View Related
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
May 2, 2013
I have a variable that gets set to an address range:
Code:
TAG_RANGE = Sheets(BAL_SHT_TAB_NAME).Range("A1", Sheets(BAL_SHT_TAB_NAME).Cells(Rows.Count, Range("A1").Column).End(xlUp)).Address
In the first loop that executes this command TAG_RANGE gets set to $A$1:$A$39
I want to loop through the values in that range and run tests against them. Is there a function that will take the values in the address range an convert them into an array so that I can use something like this:
Code:
For Counter = LBound(TAG_RANGE_ARRAY) To UBound(TAG_RANGE_ARRAY)
[run tests]
Next
Or is there someother direct way to do this other than creating a loop that fills the array element by element
View 5 Replies
View Related
Aug 5, 2013
I'm trying to write a macro which will find members of an array on a sheet, highlight the column and then change the format of the column, what I have at the moment is:
Dim datearray(1 To 3) As String
datearray(1) = "Date1"
datearray(2) = "Date2"
datearray(3) = "Date3"
For x = LBound(datearray) To UBound(datearray)
Cells.Find(What:=x).Activate
ActiveCell.EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
Next x
End Sub
The problem is, when I "F8" my way through the code, it doesn't seem to be finding the members of the array in the sheet.
View 3 Replies
View Related
Mar 23, 2008
I have a cell that contains parts that are comma separated. I want to assign each of these parts to an element in an array so I can process them using a loop function.
Example Cell A1 = t4567, g8905, z3030
partArray = (t4567, g8905, z3030)
Is there a method or function i can use to achieve this?
View 9 Replies
View Related
Feb 3, 2010
I have an array of data type Variant, who's elements are workbooks opened by a user.
The array size is static, which for now isn't a concern but I can't work out how to close the workbooks in the array via a loop and the usual vba code of Workbooks("file").Close
Code I have that doesn't work is:
Sub Close_Workbooks_In_An_Array ()
Dim dFile (1 to 6) As Variant
Dim i As Integer, j As Integer
' // Some code to open files, set each dFile(i) as a file and then process
' // them. Max value for i is 6
j = 1
For j = 1 To i
MsgBox ("Closing: " & vbNewLine & vbNewLine & dFile(j))
Workbook.(dFile(j)).Close
Next j
End Sub
View 9 Replies
View Related
Oct 17, 2007
I am trying to compare the values in an array with the values in a range of cells. It worked fine until I moved the code into a sub. The specific problem that I am having is that is pulling cell values from "shippablegoodspriorday" and it is supposed to be pulling the cell values from "shippablegoods".
module1
Call compareSheets(priorShippable, Sheets("notfound"), Sheets("variance"), Sheets("shippablegoods"), [A:A], 11)
module2
Sub compareSheets(goods() As Variant, WS As Worksheet, WS2 As Worksheet, WS3 As Worksheet, col As Range, offset As Integer)
With WS3.............
View 2 Replies
View Related
Mar 4, 2008
I am trying to figure out a method for calling the ith number in an array that was defined in another function. The mean function is working and the result is (1 x variables) array. Then, I want to use that array in the sdev function. I am having trouble pulling the ith number from the mean function. Also, is there a way to make variables constant so that they do not need to be declared for every function.
Sub stats()
periods = Range("periods")
variables = Range("variables")
Redim X(periods, variables) As Double
Redim uX(variables) As Double
Redim sdX(variables) As Double
'Load Data
For i = 1 To variables
For t = 1 To periods
X(t, i) = Cells(4 + t, 2 + i)
Next
Next
'Calculate Mean (run 'mean' function')
uX() = mean(X)................................
View 7 Replies
View Related
May 3, 2008
I create a dynamic array. I want to output all the values in my dynamic array in separate rows.
MsgBox ("the values of my dynamic array are: " & vbCr & _
myarray(1) & vbCr & _
myarray(2) & vbCr & _
myarray(3) & vbCr & _
....
myarray(i))
View 5 Replies
View Related
Mar 6, 2013
How can i split a string of "name*sirname" by the * character. At the end i want to msgbox the both 2 results as "name" and "sirname"
Code:
Private Sub CommandButton1_Click()
Dim myArray() As String 'DEFINE THE ARRAY
i = 0
[Code].....
View 2 Replies
View Related
Mar 28, 2008
I am trying to pass a string array into a form. I have added a member string array to the form, and a property to "Let" the array in the the member array.
Private sFormString() As String
Property Let FormString(value() As String)
sFormString = value
End Property
I can pass a string in using a procedure:
Sub StringArrayTest1()
Dim TestString() As String
Dim frmString As FString
but I cannot "modulate" the code, or else I get an internal error (error 51). I.e. this code doesn't work:
Sub StringArrayTest2Mod(TestString() As String, frmString As FString)
frmString.FormString = TestString
End Sub
Sub StringArrayTest2()
Dim TestString() As String
Dim frmString As FString
Set frmString = New FString
Redim TestString(1 To 2)
TestString(1) = "Cat"
TestString(2) = "Dog"
Call StringArrayTest2Mod(TestString, frmString)
End Sub
Does anyone know why this happens? Obviously, in the example code its not an issue, but the application I'm using this for is more complex, and some modulation here would be good.
View 9 Replies
View Related
Aug 23, 2012
Is there any way to do this part of code without calling out each element individually?:
VB:
Dim aSequence(7, 1) As Integer
'setup Evaluation sequence
aSequence(0, 0) = -1
[Code]......
View 2 Replies
View Related
Apr 7, 2008
I have an array of variants..
lvarArrSource = Array(12, "=F", 2, 3, 4, 5, 11, 6, 7, 8, 9, 10, "123", "F", "F", "F", "F", 11, "F", "NR", "F")
which i am using to map columns between spreadsheets. The basic numeric entries refer to columns to copy. But I want to make the routine smarter with the strings.
If array(x) = 12 (For example) Then
Do something
End If
If left(array(x),1) = "=" Then
Do something Else
End If
If array(x) Is String literal (e.g "xyz" Or "123") Then
Do a third thing
End If
i cant find a typeof or isstring kind of function. Isnumeric works ok for some values but quoted numbers (eg "123") return true (which isnt what i want). I have tried the left(string,1) = """ but excel seems to hide the quotes.
View 4 Replies
View Related
May 22, 2007
I have a form using text boxes to enter some data into a spreadsheet. These text boxes appear in pairs for any single point (e.g., name ; location). Does anyone have a code which can actually define how many of these pairs will appear on the form when loaded? For example if there are 5 points to be defined the form should have 5 pairs of these text boxes. The maximum number of these pairs is around 10 and I could make a form with a 'standard' number of 'pairs' but that's not really it. I've tried to attach the form but it did not work
View 9 Replies
View Related
Oct 3, 2012
I have this array formula
=IF(ROWS(A$11:A13)1,IF(MSB!$A13"",SMALL(ROW(Table1[APP DATE])-ROW(MSB!$A$11)+1,ROWS(A$11:A13)),""))))
the problem is that when both if statements are true i want it to get the row number, this is because table1 has blank rows (can not be avoided as the data is linked from a closed workbook)
i can see this works up until it meets the first blank row and i get #value error.
on table1 data exists on row 1,2,5 and 8 so my problem is getting the small function k to report these numbers?
View 6 Replies
View Related
Oct 9, 2009
I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.
The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.
As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report
The conditions are selected using a check box and a drop down list to select the AND/OR condition.
I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.
A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.
View 10 Replies
View Related
May 2, 2013
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
View 9 Replies
View Related
