Defining An Array To Use In For Next Loop

Feb 13, 2007

I have created some code of which this is an extract

Dim i As Variant

i = Array(37, 38, 41, 42)

For Each i In i

'Some Code
Next i

the routine works fine when the i variable is hard coded, but once the above is included it fails (Error 10 This array is fixed or temporarily locked).

View 3 Replies


ADVERTISEMENT

For Loop Object Defining

Jan 20, 2009

I tried to run this code and encountered an error telling me that N should be an object.

N has to be a variable number representing the amount of sheets in the workbook -2.

Does anyone know how this is written?

View 6 Replies View Related

Loop Defining Named Ranges

Nov 23, 2009

I'm trying to define some ranges with a loop but being a newbie at VBA I'm finding it difficult to solve the error in the code.
First, as the data area is uncertain (variation in the number of rows and columns) i look to the end of the entire data area. Then i want to define intervals of 250 rows with a 30 row difference between each of them. Here is my

View 9 Replies View Related

Defining The List Of Worksheets In For Each Loop

Dec 8, 2009

I am trying to perform certain actions on just some worksheets in a workbook. I realize this is probably a simple thing, but I've been unsuccessful in my search of the web so far.

What I'd like to do is define the worksheets (i.e. "in worksheets" below) in the below code to only include a list of worksheet names (which I can provide - ideally via a list in the worksheet in Excel, say in cells A1:A15 of a tab called "Tab List".).

View 4 Replies View Related

Defining A 2D Array By Hand

Nov 22, 2009

I have to enter a 2D array by hand into code because I am doing it in PowerPoint VBA, but I can't find out how to enter a giant array manually.

I know I could do it like this:

MyArray(0,0) = 1
MyArray(1,0) = 2
MyArray(2,0) = 3
...
MyArray(99,99) = 10000

But obviously that would be a giant mess.

Couldn't I just define it like:
{0,1,2,3,4,5,6,7,8,9,...,99,100;
101,102,103,...,199,200;
...10000}

View 9 Replies View Related

Array () Vs. Loop

Jun 13, 2008

when is it appropriate to us arr(1 to 10) Vs. For i = 1 to 10. I know this may sound like a silly question and expose my ignorance - but I suppose there is no other way to learn :|

View 9 Replies View Related

Array Name Loop Through Variables

Jun 17, 2013

I am trying to loop through different SlicerCaches but it doesn't seem to work.

Here is the code, I am trying,

VB:
Test_Name = Array("[Test - Test Allocation]", "[Test 2]")
For i = LBound(Test_Name) To UBound(Test_Name)

ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")

The code returns a mismatch 13 error.

When I try it without an array, it works fine.

VB:
Test_Name = "[Test - Test Allocation]"
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")

View 3 Replies View Related

Sum Portion Of Array Without Loop

Jun 24, 2009

I faced a problem to attach this small WB as an XLS

I'm looking for some way to some the red cells without looping.

In this example the array was filled with A1:A10 values.
In the real situation the array gets its values from other source than a Worksheet Range.

As you can see I manged to transport the Array Values to F1:F10 and from here I could calculate the sum of F3:F8 but I do not want to use any helper columns.

View 12 Replies View Related

Using Array To Quickly Loop Through Data

Jul 5, 2014

I've built a simple inventory tracking system, and decided a reporting feature would be nice. There are four categories that are entered when inventory is removed...Date, Employee Name, Item Description, Location, and quantity.

Four my reporting purposes I'm only concerned with Date, Employee name and Item Description.

I've been able to write code that does what I want using a multiple Cases and a For loop once the case is identified. However, the more data there is the longer this takes...so I decided to stretch myself and try my hand at arrays (first time really working with arrays), but I'm having trouble figuring out exactly what I need to do.

Here is what I think the steps need to be.

1. Store my data (the categories above) which are located in the Check Out sheet
2. Go through the arrayed data to find exact matches based on my search criteria (here is where the Cases come in)
3. Pull out only that data and write the information to a "Report" Sheet
4. Export that sheet to PDF (this part I already have)

Below is a copy of what my current "working" code looks like (I should mention that the search selections are made from a userform this is what the Cases are deciphering between which ones are blank etc...), also most of my variables are instantiated as Public variables within a Public_Variables module also below.

[Code] ....

And the Public Variables...

[Code] ....

View 4 Replies View Related

If / Then And For Loop - Isolating File In Array

Feb 28, 2013

I have a For loop, its an array with 5 file names in it. The loop checks for the files in the path, and if the file is there it places the file in the corresponding sheet in the workbook. If it is not, I get the option to browse for the file. Originally all 5 files were required, however now the requirements changed and only 4 are required the last file "byband.csv" is not required. So they want the option a message box telling the user the file is not required they can browse or keep going.

I worked out the second part but only by taking "byband.csv" out of the loop and writing a separate procedure for it, I wondered if in my original procedure I could isolate "byband" and if not found go to another part of the procedure. Basically, I needed to do the following:

If the file in the array byBand.csv is not found, Then:

vmbProceed = MsgBox(strFifthImportFile & strMessage, vbYesNo + vbQuestion, strTitle)
If vmbProceed = vbNo Then
Exit Sub
Else

go to Line 1 in the procedure below.

Code:

Sub import_Employee_Data()

'This is Step 1 when the frmDataImportSplash is activated.
'This procedure imports the byEmployee.csv sheet. The procedure checks if the file is
'in the same directory as the template. If the file is not there, a browser window appears to allow the user
'to browse for the missing file. A series of message boxes guide the user through the process and
'verifies that the user picked the right file. The user can cancel the import at any time.

Dim strPath As String
Dim strFirstImportFile As Variant

[Code] .........

View 8 Replies View Related

Fill Array To Execute Loop?

Apr 25, 2014

how to fill array with cells form selections and loop my macro for each cell of that array.

Code:
'this is macro I need to execute for each cell in my selection. Here I threat each cell from selection as solo selection.

Sub SELECTION_CELL_COUNT_SINGLE()
Dim z As Integer, q As Integer, X As Boolean, I As Integer, txt As String
Dim tmp As String, J As Integer, K As Integer
Dim sStrip As String

[Code]....

View 1 Replies View Related

Use Array To Store All The Results Of The For Loop

Jun 19, 2008

I would like to use array v to store all the results of the for loop u...How can Ido it?

Dim myRange As Range
Dim AnsRange1 As Integer
Dim AnsRange As Range
Set myRange = Application.InputBox(Prompt:="Select row to insert 10 rows below", Type:=8)
AnsRange1 = myRange.Row
Dim u As Integer
Dim v As Integer
Dim var() As Single
v = 0
For u = 23 To 24022 Step 9
var(v) = u
Next u
If Not (AnsRange1 = v) Then
MsgBox AnsRange1
Else
Range(AnsRange1 & ":" & AnsRange1 + 9).Insert Shift:=xlDown
End If
End If

View 9 Replies View Related

Loop Through Array And Perform Formula

May 10, 2006

trying to loop through an array, with each loop calculating a formula (VLOOKUP) for one cell based on the values of a cell in another sheet.

Not sure but I think it has something to do with looking up a text value while the loop returns a value.

Private Sub ExpandFormula()
'calculates Counttarget formula in COUNTTARGETS sheet across required range
Dim CellsDown As Long, CellsAcross As Integer
Dim i As Long, j As Integer
Dim TempArray() As String
Dim TheRange As Range
Dim CurrentValue As String

'get the dimensions
CellsDown = Worksheets("SEPARATE").Range("D9").Value
CellsAcross = Worksheets("SEPARATE").Range("E9").Value

'redimension temporary array
Redim TempArray(1 To CellsDown, 1 To CellsAcross) .....................

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

Array With Button Names, Call Them One By One In A For Loop

May 12, 2009

I am having a problem calling buttons. I have an array with button names and another array with their caption texts and I would like to call them one by one in a for loop:

For i = 1 To UBound(button_array)
Sheets("Statistic").Shades(button_array(i)).Caption = button_text(i)
Next i

But the only way I seem to be able to call them is by their set names, like:

Sheets("Statistic").statistic.Caption = button_text_ENG(i)

View 2 Replies View Related

Error Filling Array With Only Few Values Within Loop

Feb 26, 2014

I am trying to fill an array with only a few values within a loop (when XspacingA is 0.5, add the value of the Line to the array) and keep getting the error "Subscript out of range".

View 3 Replies View Related

Macro To Loop Through Sheets And Print Them Out Using An Array

Aug 4, 2009

I have created a macro that loops through and creates an array of the visible sheets. Now I would like for it to print each of those sheets out. I think my main mistake is in my declaration of the array type, since I have not worked with arrays much before.

how to make the following code operable. Currently when I run it I'm getting a "Run-time error (9): Subscript out of range" error.

View 2 Replies View Related

Loop Within Array - Transfer Selection From One Slicer To Another?

Jan 8, 2014

I need to transfer selection(s) from slicer Country to Country1 (they cannot be connected due to workbook functionality).

User can make a single or a multiple selection in the country slicer.

So basically using CUBERANKEDMEMBER I got the output from slicer Country (in cell A1 for now) and used it as input in VBA for slicer Country1

ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]")

I have to use VisibleSlicerItemsList as it is an external data source (so cannot use ActiveWorkbook.SlicerCaches(Slicer Name).SlicerItems(Slicer Valuel).Selected = True/false)

Now, when user chooses 2,3 or more countries, they will be in cells A2, A3, A4... etc.

So, if the user selected 2 countries I would need to run a following code:

ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]", "[01_Feed].[Dosage].&[" & Range("A2"]")

Now, is there any way to loop this within the array, how many cells it should take?

For i = 1 to ..
code from above
Next i

won't work because then it only takes the last value, so I kind of have to loop it within the array.

View 2 Replies View Related

Loop Through Array In VBA To Refresh Pivot Tables

Jul 24, 2014

I'm hoping to automate some pivot table refreshes. I've got a dozen pivot tables on a sheet all with different numbers. I'm hoping I can write a code that loops through the pivot table names and runs the refresh. Here is the code as it is right now. I've essentially copied, pasted, and changed the name of the pivot table for the refresh.

I would like to create an array in vba (22,21,20,19,18...) that renames the PivotTable and runs the code.

sub Refresh ()
Dim pt4 As PivotTable
Dim Field4 As PivotField
Dim NewCat4 As String

Set pt4 = ActiveSheet.PivotTables("PivotTable22")

[Code] ..........

View 3 Replies View Related

Loop Deletes Previous Entries From Array

Oct 17, 2006

I think the loop is deleting my previous entries and only putting the last results in.

For assortedrowindex = 3 To 400
targetdate = Date
Do While Month(targetdate) = Month(Date)
Redim Preserve arrTransactions(assortedrowindex - 2)
arrTransactions(assortedrowindex - 2).CUSIP = Cells(assortedrowindex, 12)
arrTransactions(assortedrowindex - 2).OrderDate = Cells(assortedrowindex, 9)
arrTransactions(assortedrowindex - 2).BuyCurncy = Cells(assortedrowindex, 2)
arrTransactions(assortedrowindex - 2).SelCuurncy = Cells(assortedrowindex, 4)
arrTransactions(assortedrowindex - 2).Fund = Cells(assortedrowindex, 7)
arrTransactions(assortedrowindex - 2).SettleDate = Cells(assortedrowindex, 10)
arrTransactions(assortedrowindex - 2).BuyUnits = Cells(assortedrowindex, 15)
arrTransactions(assortedrowindex - 2).FxRate = Cells(assortedrowindex, 16)
If targetdate < arrTransactions(assortedrowindex - 2).SettleDate Then
' Sheets("Sheet2").Activate...............................

View 4 Replies View Related

Large SumProduct Array -- Original Title:Loop Function

Dec 19, 2007

could provide me a loop function in VBA for these calcultions.

Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1

These are the calculations for E1-G1.
The process must be repeated till E50-G50

So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2

View 12 Replies View Related

Loop And Find Text In Active Cell Then Store To Array

May 5, 2012

I am trying to loop through column A and I want to store in an array where I find "App" within the cell value. I am trying to find "App" but will store the whole cell value in the array. I could not figure out the Find method, so I tried the MID function but am having no luck.

Here is my code:

Code:
Sub Arraytest()
Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long
f = 0
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To 1, 1 To lastrow)

[Code] .....

View 1 Replies View Related

Recursively & Conditionally Move/Loop Through Folder Hierarchy To Fill Array

May 7, 2008

i am use dir to put into an array all the folders in a folder then enter the first folder and repeat. the problem i am having is when it encounters a file it also puts it into the array and then errors once it trys to enter that "folder" (which is a file)

right now the code doesnt do anything but the plan is then to call another subroutine that lists all the files im looking for in a folder (that sub does work)

what am i doing wrong? all the things i can find on google show it the way i am doing it.

Sub GetDirList(topfolder As String)
Dim FolderArray() As Variant
Dim FolderCount As Integer
Dim FolderName As String

FolderCount = 0
FolderName = Dir(topfolder, vbDirectory)
' Loop until no more folders are found
Do While FolderName <> ""
If Not FolderName = "." Then
If Not FolderName = ".." Then
FolderCount = FolderCount + 1
Redim Preserve FolderArray(1 To FolderCount)
FolderArray(FolderCount) = FolderName
End If
End If

View 3 Replies View Related

Excel 2010 :: Add Single Record To Multiple Column Listbox In A Loop From Array

Mar 19, 2014

Win7/2010

I have an array PeopleList(6,320) that contains

PersonID, FirstName, LastName, Email, Phone, Notes

What I'm trying to do. I have two separate requirements:

(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()

I have two errors:

Error 1 in UserForm_Initialize()

The listbox contents need transposing! It is displaying as

Code:
1 2 3 4 5
Tom Ben Heidi Julie Mark
Smith Jones Evans Simpson Petersen
x@yo.com a@bo.com c@do.com e@fo.com g@ho.com
02071001022 02071001026 02071001027 02071001028 02071001029
Friend

When it should be displaying as
:
Code:
1 Tom Smith x@yo.com 02071001022 Friend
2 Ben Jones a@bo.com 02071001026

Is there a way to transpose the array?

Error 2 in txtSearchTerm_Change()

I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:

Code:
For i = 0 To UBound(SearchList) If InStr(1, SearchList(i), SearchTerm) 0 Then
With lstPeople
.AddItem
For j = 0 To UBound(PeopleList, 1)
.List(c, j).Value = PeopleList(j, i)

[Code] ......

How do I add a record to the listbox????

Full code for reference:

Option Explicit

Private PeopleList As Variant
Private SearchList As Variant
Private Sub UserForm_Initialize()

[Code] .......

View 5 Replies View Related

Defining Name Over 255 Characters?

Feb 17, 2014

I'm trying to make a chart viewer on the first sheet of my spreadsheet and am using this website as a guideline, [URL] However instead of the 3 charts in the example, I have 12 which makes my formula for defining a name substantially longer.

By moving my charts to the same sheet and getting rid of the INDIRECT formulas, I shortened my formula to 291 characters which is too long as the limit is 255.

=IF($G$9="Sodium",$N$34,IF($G$9="Manganese",$N$35,IF($G$9="Iron",$N$36,IF($G$9="Nitrate Nitrite",$N$37,IF($G$9="Nitrite",$N$38,IF($G$9="Nitrate",$N$39,IF($G$9="PH",$N$40,
IF($G$9="Conductivity",$N$41,IF($G$9="Turbidity",$N$42,IF($G$9="Fluoride",$N$43,IF($G$9="Selenium",$N$44,$N$45)))))))))))

Is there any way to make my formula shorter? Or to reference a cell where I place the formula?

View 5 Replies View Related

Defining A Minimum Value

Oct 7, 2008

If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem.

=IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3)

=IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3)

Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.

View 2 Replies View Related

Defining Names

Jun 16, 2009

Knowing Excel adapts its formulas upon inserting a row (i.e. a sum formula will automatically include a new row inserted in the middle of its range). Is it possible to do this with a named range. I have a pivot table that serves as a table for a vlookup, but the table sometimes changes month to month with extra accounts. I need to be able to keep the formula even though the table changes, so I figured a named range would help me out but it still doesn't accomplish what I need it to do. So basically, is there a way to make the Named Range, say AGroup (A1:C5) extend to (A1:C7) if 2 new rows are added in?

View 12 Replies View Related

Defining The Right Chart Among Several In A VBA-script

Apr 23, 2009

I have a sheet containing several charts. I have a script which I found on this site that helps me create dynamic min and max values for the axis.

Since I have several charts it seems that I need to define which chart that I want to change the axis.
This causes an error.

View 9 Replies View Related

Defining The Next Non Blank Column

Aug 7, 2009

I have a form in excel that people are going to populate, then a button which runs a macro which copies all the cells out into a new line in a different worksheet inside the spreadsheet. im going to use that sheet to formulate graphs etc, the macro works however it overwrites previous data, im assuming i need some function to calculate which is the next empty cell then use this to to copy the data to

View 12 Replies View Related

IF Formula - Defining And Range

Jan 22, 2010

I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!

1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under

blanks or zeros = unknown

View 8 Replies View Related







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