I am trying to count the number of items in an array, but want to protect against someone inserting a row and changing the position of the first item destined for the array. The first item is in F25.
TickerNum = Sheets("Dashboard").Range(Range("f25"), Range("f25").End(xlDown)).Count
Is there a way to ensure that it always picks up the value currently in f25.
Presently I calculate a worksheet and store the result in any array the size of which is determined at the start. I then enter all the results in a worksheet and get the max and min values and numerous other data. I am trying to find a way to get the max and min values without having to enter the data to speed the routine up.
this is the code that puts the results into a worksheet.
Code:
Sub Recalculate() 'Recalculates the WorkBook
Dim Calc_
Worksheets(startSht).Select
Set Output = Application.InputBox(prompt:="Please select the 1st Output Range.", Title:="SPECIFY RANGE", Type:=8)
Set OutPutLabel = Application.InputBox(prompt:="Please select Label for
[Code]....
I have a workbook that launches a form for a user to confirm (or un-confirm) some data retrieved from an Access database. I would like to put the "confirming" aspect of the process into a checkbox array. The recordset can return anywhere from 1 to around 12 records. I would like to add the checkboxes on the fly based on how many records were returned. Anyone have some sample code to show me how to add these to the form at run time?
View 9 Replies View RelatedI'm trying to create an index array that resizes its array starting location.
{=IFERROR(INDEX($V$17:$AU$17,1,MATCH(TRUE,V18:AU18
I am trying to sqaure every element in a dynamic array and display the result . I donot understand how can I select the value in the cell using VBA?
Dim Y as variant, d() as double, i as long, j as long, rows as double, cols as double
Set Y = Application.InputBox("select the matrix: ", Type:=8)
Rows = UBound(Y)
Cols = UBound(Y, 2)
ReDim d(1 To Rows, 1 To Cols)
for i = cols
d(1,i) = ______==>
How do I select the value of element in that particular cell and how do I sqaure it?
I know
cells(rowindex, columnindex)
is used to select a particular cell but If I have a large array it would be difficult to go cell by cell and sqaure it.
How can I make a dynamic array public?
I have to Dim it so it stays valid only inside the sub.
Public MyVar()
Sub test1()
Dim MyVar()
ReDim MyVar(1 To 4)
For x = 1 To 4
MyVar(x) = "ffffff"
Next x
End Sub
Sub test2()
For x = 1 To 4
Range("A" & x) = MyVar(x)
Next x
End Sub
Error I get is 'MyVar(x) = Subscript out of range'
I am trying to replicate the following code using Cells references so that the row can be made a variable. I basically want to make the search array smaller with each loop.
LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range("B3:B250"), 0)
The above works, the following two variations don't:
LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range(Cells(ArrayStart, 2), Cells(250,2), 0)
LMatch = WorksheetFunction.Match(Cells(3, 3), Sheets("Product Matrix").Range("B3:B250").Item(ArrayStart, 2), 0)
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....
Excel 2007 is my version. I'm trying to build an array from a dynamic range. I will know the column letter and lastrow.I think I could do this via a FOR and NEXT routine but there has to be a more efficient way. Here is my inefficient code idea:
Code:
Dim orgctarray(65000) As Variant 'because I don't know how long the array will need to be
Dim cnt as integer
Dim startrow as integer[code]....
So as you see, technically, I need to take sheet1.range("B3:B" & lastrow) and turn it into a vb array (I don't want to copy the data to some hidden worksheet and do the work there)I'd also like to remove duplicates and sort orgctarray alphabetically after I have completed building it.
(Auto)Filter or a listbox somehow functioning like it?
Hi Excel guru's, i've got the following question:
Can I fine-tune the AutoFilter function so it filters more flexible? ....
im currently using a static array to select multiple sheets at once which works alongside another Sub
Sheets(Array("Group1", "Group2", "Group3", "Group4")).Select
i have a button that creates a new sheet and names it "GroupX" X being the next number, it automatically adds on the right number by itself and it works fine...
i want it my array to be dynamic so that i dont have to manually edit the array and the macro every time i add a new sheet.
i have tried things like:
Dim MyArray As Variant
Dim Shts As String
K = Sheets.Count - 4
For p = 1 To K
Shts = "Group" + p
Redim MyArray(K) As String
MyArray(K) = Shts
Next p
Sheets(MyArray(K)).Select
the built in "Join" function can join all elements of a 1-dimensional array into a string with delimiter. Now, how do I do that with multi-dimensional array if I just want to join 1 dimension of it.
For instance, I have:
m = 10
n = 20
Redim my(m, n)
'assign values to array here...
'I want to join, say, my(5, 0 to n) only
'How to do that without declaring a new 1 dimension array?
Also, I want to write a join sub/function to do the above for n-dimensional array, do I need to write each one for every number of dimension (1 sub for 2-dimension, 1 sub for 3 dimension, etc.)?
I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.
Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.
How do I make it so that it will select sheets if there are less than 4 sheets present?
I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?
I've am array formula that I use to provide highest values:
Code:
{=INDEX(Data!$D$2:$D$128, MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))
=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-MIN(ROW(Data!$D$2:$D$128))+1)),
MATCH(LARGE(Data!$D$2:$D$128,ROW(A1)), INDEX(Data!$D$2:$D$128,
MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-
MIN(ROW(Data!$D$2:$D$128))+1)), , 1), 0), 1)}
I want to replace the range Data!$D$2:$D$128 with a dynamic reference: like that one:
Code:
ADDRESS(MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+1,
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")&":"&ADDRESS(
MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+COUNTIF(Data!$Q$2:$Q$10000,Summary!$B$1),
MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")
The two formulas work well in separate sheets but crash when put together, how could I possibly insert the second address formula into the first one?
I have 7 sheets including the table as you can see on the attached. Now, each sheet is allocated to individual items e.g. item 10, item 20 and so on. I can't figure out how to return all the Item 10, 20, 30 ... to the corresponding sheet. It says Item 40 to all sheets.
View 8 Replies View RelatedI found quite a few posts about this problem, but none of the answers was any use to me. I need to redimension a 2 dimensional array in a Sub. I deleted all the code that is not of interest:
View 3 Replies View RelatedI have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies View RelatedI have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies View RelatedSay I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
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.
I want to pass an array to Offset in the "Height" parameter, without having to type the array.
{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}
I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.
How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??
I am trying to filter an array (the longer one) using the shorter array as the criteria. I am currently doing this using the following method
IF(LOOKUP(lookup cell, array)=lookupcell, lookupcell, "FALSE")
I then copy and paste 'values' and filter out the 'false' to get my final result.
This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.
This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.
wondering is there a VBA equivalent of --() in excel that turns trues and falses to 1's and 0's?
View 14 Replies View Related