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.
I have been exhausted with a workbook I have been working on for quite some time now, and hope I can get some help here.
I have the following array formula in Sheet1, S2 and copied down to S10051:
I figured out exactly what I wanted to do and got it to work in a test excel sheet. However, when integrating it into the actual workbook I wanted, I was unable to get it to work. So, I used the same cell references I need to work in my actual workbook and pasted it all back to the test excel sheet.
So, the test excel sheet has the working formulas up at the top, and a duplicate of what I need to work in the actual cells I need them to work in. Changing the shift start time should group any persons with the same shift togethor. I'm still fairly new, but I think the only portion that could have been changed is the portion that says 1:1.. I figure that is relative to the array so it shouldn't be changed, but not sure what else to do.
Here's an array formula that works:
[Code]....
Here's the array formula to be in the correct cells that doesn't work:
[Code] ....
How to to get it to work in the different cell area.
Attached File : Shift.xlsx
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 RelatedI 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 & "")
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).
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] ....
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] .........
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]....
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
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) .....................
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.
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)
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 RelatedI 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.
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.
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] ..........
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...............................
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
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] .....
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
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] .......
I have a few thousand files that I'm trying to hyperlink into excel. The files are named with a date at the end of it. I want to be able to hyperlink to that file just by looking up the name without the date portion. So basically I'm asking if I could hyperlink a file with only a part of the name, and yes the names are distinct even without the date. I now am loading all of the file names into a spread sheet via a VBA code, however with almost 10k files that process takes longer than I'd like.
View 7 Replies View RelatedI'm trying to sort a column of email addressees to determine how many have the extension .ac - basically I need to know how many student addresses are present in the column.
View 9 Replies View RelatedI find the protection options of Excel confusing. I have a pivot table. Alonside it our a few formula columns. I want to protect only the columns outside the pivot but can not get it to work. I tried this:
1) Highlight entire sheet
2) Format Cells Protection - remove checkmark from: Lock Cells
3) Highlight 4 rows outside pivot
4) Format Cells Protection - place checkmark in Lock Cells
5) Tools Protection Sheet - supply password
The result is that the 4 columns outside the pivot are indeed locked BUT SO IS THE PIVOT...ie you can not utlize the dropdowns!
It must have something to do with the pivot, on a regular sheet (no pivot) it works fine. Further if, while Protecting the sheet, I click the: allow Pivot table reporting box, then it allows dropdown usage but blows up as soon as you select something with an error about not being able to redisplay selected itemsbecause of protection being on....
I want to lookup a text value in an array (vlookup). However, instead of searching for the exact value I want to search for only the exact match of the first four characters of the text value. Example
If I have in Cell A1 the text value: 5154
In cell B1 I want to lookup 5154 in the values cells C1 to C6
614a215g
515476
29421cg
215a786
914215g
2154a6
The result would be 515476
I tried with this:
=VLOOKUP(A1,LEFT(Table4,4),1)
I have read Dave Hawley's reply to a member using LEFT and MID but I can't seem to make what I want, to work.
I have an address in the follwing format:
12 MAICH ROAD, MANUREWA CENTRAL, MANUKAU CITY
I need to extract the '12' to be placed in one cell, and the 'MAICH ROAD' in another cell.
Unfortunately not every address has a street number and is sometimes listed as follows:
LEYTON WAY, MANUKAU CENTRAL, MANUKAU CITY
I still need to be able to put the 'LEYTON WAY' portion into the same column as the 'MAICH ROAD' in the previous example.
how do i get the numbers after the point.
View 9 Replies View RelatedTransferring a portion of a date from one excel to another. On the file attached, the 1st column lists the date, and the second column needs to show the date in text format.
3-Jan-14 —> “January”
How this can be done?
Attached File : Month Problem.xlsx
I was wondering whether it is possible to have excel ignore the text portion of a given cell. Or alternatively, have excel add in units (or specified text) after you input an initial value so that the cell can be used in a formula.
I have noticed that if I have cell A1 as 1 min and autofill, excel will recognize the number value and still put 2 min in A2, 3 min in A3, etc. However I can not use a function or any formula as the result will be #Value or another error.
I am looking for a way to be able to use the cells in formulas, and I do not want to put the text portion of the cells into another row/column.