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