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


ADVERTISEMENT

Isolating Data...?

Nov 27, 2008

I have a spreadsheet with 2 sheets (Railcar Record & Switch) They both contain the same column names and will contain the same data. I want to do, is to have the user type in the Railcar # on Switch, and have all the information in the columns on this sheet fill in. As you can see and as previously mentioned, the information is all stored on the Rail Car Record, I just need to isolate certain information on the switch sheet.

View 2 Replies View Related

Isolating Worksheet_Change

Jan 28, 2009

I have this code in my worksheet,

Private Sub WorkSheet_Change(ByVal Target As Range)

Dim response As Integer

If Range("AD2").Value = "1" Then
response = MsgBox("Rental Agreement Does Not Exist. Do you wish to continue entering information for", vbYesNo)
If response = vbYes Then
MsgBox ("Add")
ElseIf response = vbNo Then
Range("E2").Value = 0
End If

View 9 Replies View Related

Isolating Specific Data

Jun 16, 2009

I have data that I'm copying from a PDF and pasting in an excel sheet. The data that I need is mostly in groups of three rows, but sometimes there will be two extra rows under each data group. The two extra lines are extremely similar to two of the lines of data I need, so I can't think of a way to isolate them with a filter, and each document is 15,000 rows long, so doing it manually doesn't seem to be an option.

One of the lines of data that I need always starts with a dollar sign, so my thought is I could cut those so there would be a blank cell, and then I would just need a way to select the two rows immediately above every blank cell, leaving the two data rows I don't want below every blank cell.

View 9 Replies View Related

Isolating Race Numbers From Text?

Oct 29, 2012

I have the following examples of text in column A

Race 1 - NORTHAM FEED & HIRE MAIDEN STAKE (SKY2)
Race 12 - NORTHAM MAIDEN STAKE (SKY2)

I want to isolate the race numbers so thy are in column P as follows

1
12

View 8 Replies View Related

Isolating Text And Number Strings

Nov 14, 2013

I have an address column in which multiple strings of text and numbers have been combined. I need to isolate each string and place it into its own column.

Column looks like this....

ADDRESS
615 NE CORDER AVE, LEES SUMMIT, MO 64063

2661 NW LEES SUMMIT RD, LEES SUMMIT, MO 64064

104 SE 4TH ST, LEES SUMMIT, MO 64063

4944 SW GULL POINT DR, LEES SUMMIT, MO 64082

3904 SW PRYOR RD, LEES SUMMIT, MO 64082

329 NW BRADFORD ST, LEES SUMMIT, MO 64064

They need too look something like this...
No.
Suffix
Street
Type
City
State
Zip

615 NE Corder Ave Lees Summit MO 64083

The problem I'm having when trying to use a "left" or "right" formulas is some of the strings have different positions in the whole string itself (does that make sense?) (some addresses have 4 characters, some have 3, so on....)

View 3 Replies View Related

Isolating Text That Occurs Multiple Times In Cell?

Jan 15, 2014

I want to isolate text on either side of dashes ( " - " ) that occur either 2 or 3 times in a single cell.

Example:

Apples - Pears - 99 - 100+ years
Bananas - 56 - 57+ years

In particular, I would like to capture the "99 - 100+ years" and "56 - 57+ years" text on its own.

I tried this " =SUBSTITUTE(A1, "*- ", "", 1) but it doesn't work

View 5 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

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 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

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

VBA - Look At Next File In For Each Loop?

Apr 26, 2013

I would like to know if there is a way to compare the current file in a loop with the next file in the loop without writing two loops. Here is the code I have, and in red is the code I would like to have it do. I just don't know if it's possible with this type of loop.

Code:
preface = Format(lPrefaceNo, "0000")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set mainFolder = objFSO.GetFolder("S:")

[Code]....

View 1 Replies View Related

FTP File Download With Loop?

Jun 2, 2014

I have the following code that I want to download everyfile at the listed ftp site to a folder on my computer. I was thinking about trying to make a key with dates in thisworkbook sheet1 starting in cell A1 and have it loop through.

Code:
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

[Code].....

View 1 Replies View Related

Loop Through Folder And Add Cells From Each File?

Mar 20, 2014

I have a folder that has multiple files that i would like to pull data from. This data is in the same cell in each file so all i would like to do is add the total value up at the end once it finished looping through all the files and display it on my sheet. Some of these files could have 1 worksheet where others might have 3-4. If I only have 1 file in the folder it works perfectly fine but as soon as the second file opens i get a Subscript Out of Range error (See code below for the line this error occurs on).

[Code] .......

View 6 Replies View Related

Loop Filter Then Save New File

Mar 8, 2012

Is there a simple way to loop through and filter the values in col1, then save it to a file named by the filtered value? Would be good if it could keep all the formatting as well.

View 1 Replies View Related







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