Create An Array In VBA
I am trying to create an Array, and have searched through many threads on this website and realise I don't have a basic understanding of Arrays. Specifically the ReDim Array function.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Array Of Variables And Sort
I have a dynamic number of rows each with three colums of values. These varaibles I want to fill an array with but I don't know how. Now I've just "concated" these three variables into a string and then I intend to split the string into rows by the third comma. (see below) I think it's easier though to use an array and I really appreciate some assistance. Please tell me also the best way to sort the array. It will be sorted by var_Status which is an integer. (sort order: max positive to max minimum) PreString = PreString & var_StartWeek & ", " & var_Status & ", " & var_Totalh & ", "
View Replies!
View Related
Create Standard Deviation From Array
I am fairly new to VBA, i require some help creating a Standard Deviation UDF using arrays, i have made a userform that allows a user to select anywhere between 1 to 30 companies (A1: AD1) and its corresponding stockprices (A1:A60) in a sheet called companydata, using the following sub, my problem is that i am unsure how to create a function using the info the user selects. I have also created a returns function (below) but this is vba is saying that there is a out of range error, Will anyone be able to help please. Below is the code i am using to allow for my sub: ....
View Replies!
View Related
Create A Dynamic Array So That When A User Enters The First Sheet Name Via An Input Box
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?
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Create Array Of File Names/sheet Names
Two part question: 1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction? 2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
View Replies!
View Related
Array Formula In VBA
I have a lengthy array formula that I try to enter using VBA. I get the common run-time error. I tried breaking it down, but that did not work. Maybe the syntax was wrong in the break down. Can some help out please? =IF(COUNTIF($B:$B,B2) 1 Then Range("B" & x).EntireRow.Delete End If Next x Application.ScreenUpdating = True
View Replies!
View Related
VBA Initial Array Value
I have declared arrays for the variables in the code segment below. There is however a problem with the way I've initialized C and F as I the compiler stops C(1) and displays the message: "Compile Error: Expected Array." For the calculation to proceed correctly, the first values of the C(i) and F(i) arrays need to be one and zero respectively. Is there a way to initialize these variables to these values? Art C(1) = 1 F(1) = 0 G(1) = Y(1) B(i) = C(i - 1) + Q C(i) = (1 + B(i) ^ (-1)) ^ (-1) F(i) = 1 / (1 + B(i)) G(i) = F(i) * G(i - 1) + (1 - F(i)) * Y(i) arrKAL1(i)= G(i)
View Replies!
View Related
VBA Array Basics
Lets say I have this.... PHP Dim B as Rangeset B = activesheet.range("A1:C3")Dim a(3) As Doublea(1)= 1.2a(2)= 3.6a(3)= .27 B is a 3x3 Matrix of values in your sheet How would I perform matrix multiplication of B * array "a"? My main problem is with how to reference then entire array "a" and how the data is being stored (is the array a 3x1 or a 1x3). And if there is a VBA MMULT command or if building my own or using the worksheet one are my only options.
View Replies!
View Related
Median Of VBA Array
Hi, Im using a array in Excel VBA, i got a method which looks at a element no of the array as as a return for that array, i have got it to sort the array, i just need some help to find the middle number (median) of the array.
View Replies!
View Related
VBA Control Array
I have a form that launches when a user wants to confirm an order from a customer. It loads a drop-down combo box with our current customer list. When the user selects a customer, a checkbox array is loaded with the items that a customer has ordered. The checkboxes confirm (or un-confirm) that the order has been processed. Everything works fine up to this point. Let's say the user selects a customer, and this customer has 7 orders. 7 checkboxes appear on the form, either checked or unchecked. The problem is: when the user selects another customer from the drop down that only has 3 orders. The 3 checkboxes appear just fine, however, the balance of the previous customer (orders 4-7) also still show on the form. How do I remove the previous checkboxes from the form and prepare for a new recordset (or new checkbox array). I have tried to run backwards through the code that creates the array using Me.Controls.Remove instead of .Add, and this is just not working.
View Replies!
View Related
VBA Headers And Array
I just joined this message board after having very little success in altering my VBA code. I have a data file and when I run the VBA code, it only works partly. Currently when I run the code, it will create new month headers, but only for a 12 month period and it is based off the current date on the computer. I need this to go to whatever specified date range I choose and I especially need it to go for more than 12 months. I am also having some issues because when my code runs, it is not running back far enough and there are gaps in the analysis that it does. For instance, after changing the time on my computer to 2009, project A and B's data stops in June 2009, even though the define phase is occuring before that. I have attached the code.
View Replies!
View Related
Array Variables In VBA
Is there any way to add together a portion of an array without using a loop or for statement. i.e.) I have a array variable: arrayvar = array ( 1, 2, 3, 4, 5) is there a way to sum the last two numbers in the array without using arrayvar (3) + arrayvar (4) or a loop or for statement. I am thinking something along the lines of worksheetfunction.sum( arrayvar( 3 to 4)) I know that this does not work but I hope you can see what I am trying to do
View Replies!
View Related
Create Graph Using VBA
I want to create excel graph using VBA and found difficulties on chart name set to default. the script is like this : ActiveSheet.ChartObjects("Chart 31").Activate Can I set my own name of the chart instead of "Chart 31" (excel default) ?
View Replies!
View Related
VBA To Create New Sheet ...
I would like to be able to have a macro, linked to a button, so that when you press that button it creates a new named sheet based on data in a particular cell. I.e. There is a table with the following data: Apples (A1) Oranges (A2) Pears (A3) There are also the sheets Apples, Oranges & Pears. You enter 'Grape' in cell A4, then when you press the button it creates a new sheet named Grape. Also when a new sheet is created, the 'input cell', in this case A4, is moved down one so that when the next sheet is needed to be added, it doesn't just add the same sheet (i.e. in the example above, we don't end up with a new sheet named 'Grape' each time we click the button).
View Replies!
View Related
Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this: slow windows xp windows xp running slow windows xp computer [slow windows xp] [windows xp running slow] [windows xp computer] [computer running slow windows xp] "computer running slow windows xp" "slow windows xp" "windows xp running slow" "windows xp computer" You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Replies!
View Related
Create VBA InputBox
I am trying to create an inputbox using code from this site: Sub NumericDataType() Dim lNum As Long On Error Resume Next Application.DisplayAlerts = False lNum = Application.InputBox _ (Prompt:="Please enter you age.", _ Title:="HOW OLD ARE YOU", Type:=1) On Error Goto 0 Application.DisplayAlerts = True If lNum = 0 Then Exit Sub Else MsgBox "You are " & lNum & " years old." End If End Sub I get an error on the first line that says, "Compile error: argument not optional".
View Replies!
View Related
Entering A Formula As An Array In VBA
My VBA sub contains DIM sFormula As String DIM rCell as Range at some point in the code, the value of sFormula is set to something like =ROUND(SUM($A$5:$A$10*$B$5:$B$10),2) (not really this formula but of that flavour) later in the code I have an instruction rCell.FormulaArray = sFormula (rCell is set to a single cell at the time) When I run the code and then examine the cell, I find that the cell's formula has been correctly set to =ROUND(SUM($A$5:$A$10*$B$5:$B$10),2) however there are no {curly brackets} surrounding the formula to suggest that it has been entered as an array.
View Replies!
View Related
Maximize Performance Of Array In VBA
I'm trying to write some code that is performing operations on large arrays, using a pair of For loops. In languages like C++ and Fortran, it makes a big difference to the performance of the code which way round you put these 2 For loops, due to the way that array data is stored in the computer memory. However, for VBA, I've no idea which way round this is. For an array, A(i,j), do I want For i = 1 To N For j = 1 To M A(i,j) = Cells(j, i).Value Next j Next i
View Replies!
View Related
Sort An Array In VBA Memory
two parts: 1. The following is the VBA version of what i've been using: Range("A1:J1000").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _ Range("E2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlDescending _ , Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal now arrays are bigger than 1000 rows>> even bigger than 65536. Such an array is stored in "arrBIG" and dimed as (1 to 10, 1 to 100000). The goal is to sort it by the 4'th, then 5th then 6th values in the first dimension - the one that is 1 to 10 - i call this the column. arrBIG.Sort Key1:=4, Order1:=xlAscending, Key2:= _ 5, Order2:=xlAscending, Key3:=6, Order3:=xlDescending _.................
View Replies!
View Related
Sorting Array's In VBA
I have an array that is 10 rows by 4 columns. I would like to sort the whole array based on sorting column 1 in descending order. Any good ideas to edit or write new code? Anything to send me in the right direction would be sweet as well!
View Replies!
View Related
VBA: Evaluating Values Against An Array
I am trying to loop through some rows within a worksheet in an effort to make sure the values match a list of values that are defined in an array. However, when I get to the IF statement, I always get a 'Type Mismatch' error. Dim varRetailers varRetailers = Array("Depot", "Lowes", "Sears", "TSC", "Walmart", "Z-Other") For c = rowDataStart To rowDataEnd If Cells(c, colRetailCat) varRetailers Then '
View Replies!
View Related
VBA Selecting A Variable Array
I've successfully copied the array of equations using the VBA that Pjoaquin enlightened me with from my last thread. The outcome was Sheet2!A2:O2 being successfully populated with the equations from my first sheet... but here comes the problem: I'm looking to autofill A2:O2 down to the last record in Column P. But the number of records in this table is varable.
View Replies!
View Related
Enter Array Formula Via VBA?
I have following Array formula in Cells N1:N3. HTML =SUM(IF(O$4:O$1000="Total I/O Points",N$4:N$1000,0)) Each formula has different text in "". There are a few macros in this workbook that delete and/or insert rows. When that happens, the formulas listed above get modified with different references. How do I restore the formulas back to the same range reference [O$4:O$1000]?
View Replies!
View Related
Sumproduct In Vba (formula Array)
I have a function that has a formula sumproduct and here it is: Set RSheet = Worksheets("Rawdata") Set RSheet = Worksheets("Rawdata") RSheet_lastRow = RSheet.Cells(Rows.Count, "A").End(xlUp).Row RSheet.Range("AJ2:AJ" & RSheet_lastRow).Formulaarray = "=SUMPRODUCT ((V2>=ProjectedStarts!$K$1:$K$45)*(V2<=ProjectedStarts!$L$1:$L$45),ProjectedStarts!$M$1:$M$45)" The problem is that when the formula is sent to each cell V2 (bolded) does not change to V3, V4, V5.
View Replies!
View Related
VBA - Selecting Sheets In An Array
I am trying to clean up a macro a little that used to look like the following: Sub Refresh() Sheets("Sheet2").Select ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False ActiveSheet.QueryTables(2).Refresh BackgroundQuery:=False Sheets("Sheet3").Select ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False I want to set up an array and have the code run through a loop and have gotten stuck on the following: Sub Refresh() Dim i As Integer Dim SheetName As Variant It does not seem to want to go from the first sheet to the second. What am I doing wrong/what can I do better?
View Replies!
View Related
Use Trend Worksheetfunction On Vba Array
I am trying to use the TREND worksheet function on a vba array. I keep getting Runtime error 1004: Unable to get the TREND property of the WorksheetFunction Class. I can bypass the error (like it were a search with no result), but I get no result of any kind from the function. My search of other threads yields nothing concrete, but causes a guess that it may have to be used on a worksheet range not a vba array. But that's not actually stated in any of the threads, and I find no documentation to that effect. Is this the issue or is it something else altogether? Sub test() Dim knowny As Variant Dim knownx As Variant Dim newx As Integer Dim myval As Integer knowny = Array(2, 4, 6, 8, 10) knownx = Array(1, 2, 3, 4, 5) newx = 6 'On Error Resume Next myval = Application.WorksheetFunction.Trend(knowny, knownx, newx) ' 'next error, err as normal. ' On Error GoTo 0 ' Err.Clear Debug.Print myval End Sub
View Replies!
View Related
Create Comparison Code In VBA
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank. What I currently do manually is: 1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2. 2: Highlight the differences 3: Move onto the next employee ID All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row). As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
View Replies!
View Related
Create A New Workbook By Vba Of Excel
I want to the following steps to be done by vba of excel. 1. copy ranges of cells in the active worksheet (sheet3) 2. add a new workbook and paste the copied ranges to sheet1 3. save the new workbook with the new file name (e.g. date) 4. close the new workbook and clear the contents the copied ranges of cells in old workbook. following code and make it more simpler? Worksheets("Sheet3").Activate If Range("l1").Value <> "" Then Range("l1").Value = CDate(Date) End If asdate = Range("l1").Value 'date entered by user resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data") If resp4 = 7 Then Exit Sub
View Replies!
View Related
Vba To Create A New File From Spreadsheet
I have the following spreadsheet: A-------------B File No.-----PRODUCT TYPE 123456-----NDT 123456-----NDT 123456-----NDT 123456--------- 123456--------- 789110--------- 789110--------- 789110--------- What I need is if there is an NDT in Column B and 123456 in Column A then I need to eliminate all rows which contain 123456. End result A 789110 789110 789110
View Replies!
View Related
Create Outlook Appointment From VBA
I am probably overengineering my macro, but I would like to setup an outlook appointment in two peoples calendars from my userform. The macro has access to the email addresses of the selected persons, I just want to send date/time and subject.
View Replies!
View Related
VBA To Create A New Folder From A Textbox Value
I don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox? I have made a userform that I use to collect a persons first name & surname. I then created a folder in a directory manually (e.g Z:spnzphotos) I then scan their photo and place it into the folder. Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?
View Replies!
View Related
Remove Empty Array Items In VBA
I have an array MyArr() in excel. Its length is from 1 to i where i takes dynamic value from varibable. Now the problem is MyArr(1 to i) has some empty values. Like say: if i = 5 then MyArr(1) = "a" MyArr(2) = "" MyArr(3) = "b" MyArr(4) = "" MyArr(5) = "c" How can I get rid of those empty elements so that MyArr() becomes only three elements long and then display them in a range of three cells.
View Replies!
View Related
VBA Sort String Array Function
I have a string that I need to sort. Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well? Function Alphabetize(ByVal sText As String) As String Dim sWords() As String, sTemp As String Dim i As Long, j As Long, n As Long '-- clean up text For i = 1 To Len(sText) Select Case Mid$(sText, i, 1) Case " ", "a" To "z", "A" To "Z" Case Else: Mid$(sText, i, 1) = " " End Select Next '-- remove leading and trailing spaces....................
View Replies!
View Related
Copy Array Formula To Other Cells Via VBA
{=AVERAGE(IF(('Sales Reports'!$A$1:$A$5000=$A3)*('Sales Reports'!$C$1:$C$5000=$C$1),'Break Reports'!$D$1:$F$5000))} and am copying it to other cells using this Sub copyformula() With Sheets("Sheet2") .Range("B3:B5000").Formula = .Range("B3").Formula End With End Sub It does the copy but removes the { and } thus rendering the formula useless to me. How can I accomplish this task and keep the formula as an array formula? I tried to drag the formula down and recorded it as a macro but when it runs it takes far to long.
View Replies!
View Related
VBA Array Function For Polynomial Coefficients
I have a VBA function that calculates polynomial coefficients for a series of data pairs. One selects the range of cells that the coefficients are to be stored in, and enters the polynomial formula: {POLFIT(Xa, Ya, N)} Where Xa is the array of ordinate values, Ya is the array of data values, and N is the polynomial order to be fit. It is obvious that one needs to select at least N+1 cells when the array funtion is typed in. But, it is easy to select too few cells. I am looking for a way to test whether enough cells were selected for the range formula: The function declaration is Function POLFIT(Xa, Ya, N As Integer) As Variant Various means I have tried to count POLFIT do not return the correct value.
View Replies!
View Related
Pass Cell Values To VBA Array
i'm trying to do my homework which requires me to pass values from an array in excel worksheet to VBA and print it in a msg box i've tried dim arr as variant arr = range("A1:A6").value msgbox arr but it didn't work. that's the first step of the homework the second is i have 2 list of array year:1999 - 2002 profit:10,20,30,40 i have to find the max profit and get the year when it occurs
View Replies!
View Related
|
TRACKER
