In each VBA book I've read the authors will write their code like this:
nStart = UBound(GenArray1)
nEnd = UBound(GenArray2)
Redim preserve NewArray(nStart To nEnd)
Instead of the less line method of:
Redim preserve NewArray(UBound(GenArray1) To UBound(GenArray2))
I have the following code on a userform. The form has several checkboxes used to determine which states to include on a report. I was wondering if anyone could think of a better way to code this. The code goes throught the checkboxes and assigns values to variables state# if the box is checked. I need the state# variable to fill in sequentially, so 1 first, 2 second, ect. Any boxes not checked will result in some state# = "". I also need to keep the order the states are checked consistent. If Arizona checkbox is checked, I always want it to be State1.
View 3 Replies View RelatedHave two ranges:
Range("Master") 5000R x 500C
Range("Filtered") 5000R x 500C
1st Column in Master is a calculated trigger column that sets itself to the row number if it needs to be copied to the Filtered range or is 0. .
Currently using two loops:
Loop 1 Build Collection of unique row numbers from the trigger column.
Loop 2 Process Collection: range copying row values from master to filtered.
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
I am currently using Excel 2003 to pull from 2 external spreadsheets to compile data. The first sheet (seniority list.xls) being referenced contains all of our employees (approximately 350 rows, but that can vary from 340 to 450) as well as some information (employee ID, full time or part time, etc.). The second sheet being referenced (pay.xls) contains a breakdown of all employees and their pay for a particular bi-weekly period.
What I'm looking to do is:
(1) Find out all of my current employees. I do this by referencing seniority list.xls.
(2) For each employee, find out how many hours they worked each week. I need to do this by referencing pay.xls. Unfortunately, that file works quite differently than seniority list.xls. Unlike the latter, which contains one employee number per row, pay.xls could contain as many or as few rows per employee as their schedule dictates. Each row contains things such as overtime, regular time, double time, alternate regular time, alternate OT, vacation, sick pay, etc. In total, pay.xls could contain as many as approximately 8000 rows. To determine how many hours they work, I'm using this CSE (array) formula:
(Note that the employee ID is in column A of the current sheet.)
I have a question about loops. I've read that loops are slow and there are better ways to achieve the same results but I do not know how to accomplish the same without using a loop. I've attached a code that looks in the first sheet and if in column "f" if criteria is met it will copy the row to a separate sheet. Then is goes to a separate sheet and does the same thing after it skips a row then copy header information. What i'm doing is consolidating multiple sheets to one for prioritizing data. The loop works but I've been going back and trying to make my workbook a little more effecient and really cannot figure out how I would do it differently.
Sub AVAILABLEBILLETS()
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application. ScreenUpdating = False
Dim j As Integer
Dim I As Integer
Dim K As Integer
Dim fillcell As String
I have a bunch of array formulas that need to be activated by going on the cell, hitting F2, then hitting Ctrl+Shift+Enter. In the attached sheet I have done a record macro to automate this. I have another sheet with something similar just a lot more items on Sheet1 (~250 items). It will be very time consuming for me to record macro and hit F2, then hit Ctrl+Shift+Enter ~250 times so I am wondering if there is any way to activate all the array formulas on the sheet in one go using 1 formula. I'm not too concerned with file size.Also, not all arrays only include columns A and C, other columns (D, G, H and J) are included too.
View 3 Replies View Relatedfollowing code be write down in more efficient manner :>>>
Range("B3") = Range("L" & Target.Row)
Range("C3") = Range("O" & Target.Row)
Range("D3") = Range("Q" & Target.Row)
Range("E3") = Range("R" & Target.Row)
I'm using the script below to do a cut & paste job. The script works fine. However, since I have a lot of data on my worksheet, it takes a very, very long time to complete the job. Based on the same logic used in my script, could you propose a more efficient way of doing it?
Sub test()
Application. ScreenUpdating = False
On Error Goto errortrap
Do
Columns("d:d").Select
Selection. Find(What:="*", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell. Offset(0, 1).Activate
ActiveCell.Offset(0, -1).Activate
Selection.Cut
ActiveCell.Offset(5, -2).Activate
ActiveSheet.Paste
Loop
errortrap:
End
End Sub
Was hoping to get some help with a mini project of mine.. Currently I am looking to create a progress/commission report (pull data from an access dbase) and populating an excel sheet. I can manage to pull in the data fine its just gettting into a format that is the complicated thing. I was hoping that someone might have a few moment to pick thru my code (see attached) and maybe suggest an alternative/more efficent way to do the refreshing.
View 5 Replies View RelatedI have a lot of data with the wrong number format. I want to find all cells with this particular number format and change it to another.
I know I can do this by looping through all the cells on the sheet or by using the find method, but both ways require looping. I am hoping there is a quicker way. I originally thought there was a SpecialCells type that was format conditional (not conditional formatting), but there doesn't seem to be one.
in a selected columnar range.
I want to write a fairly simple, fast macro to examine a selected range and simply delete the row for each blank cell that it finds in that range.
I have read around that loops aren't necessarily that fast and some of these ranges could be 1000s of rows long...
way to write something like this in VBA?
the sheet is very long and monitors the sales activities of each individual every day extending vertically down the sheet until the present day.
my first priority was to get the information recorded but now i am wanting to use it.
how would an expert arrange this data to make analysis easier going forward?
ultimately i will be wanting to look at individuals, groups of individuals, certain types of activity etc within various time periods.
Is there a way to make the code below more efficient?
My data resides in "Buildings August 2006(2).xls " and I wish to copy some cells to "Backlog Analysis .xls"
'Copy J16 to B5
Windows("Buildings August 2006 (2).xls").Activate
Range("J16").Copy
Windows("Backlog Analysis.xls").Activate
Range("B5").PasteSpecial
I repeat the code above 7 more times to handle J29-B6, J33-B7, J42-B8, L56-B10, L57-B11, L60-B13 and L62-B14.
Is there an Array I can use to encompass all of the copy and paste commands?
This is step 1 of something I am doing for work
Step 2 will be to figure out how to work my way through all of the worksheets in "Buildings August 2006(2). I have seen numerous code examples for this so I think it shouldn't be too hard.
I was tasked with looking through a 10,000 row by 20 column spreadsheet for 628 different ID Codes and replacing them with their Descriptions. The ID Codes could appear individually in any of these cells (200,000 cells!).
The list of ID's and Descriptions hardly ever changes, so I decided to create the two-dimensional array as part of the find-replace macro shown below (only a few lines of each dimension are shown).
My question isn't related to that (although if you can suggest a better alternative please do). The main "work" of the macro is the loop at the end of the array declarations, which essentially loops through all 628 ID codes in array dimension 1 and does a Find-ReplaceAll with its corresponding Description from array dimesion 2.
This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop, but I was hoping one of the gurus might suggest an even better method than 628 loop iterations. If not, so be it, the end users will appreciate what I've done and then have to find something to do with their "free time."
I'm trying to move my VBA programming to the next level and use more efficient code.
I'm wondering if there is a faster way to run the loop below, perhaps removing the 'For c = 2 to LastRow' as it seems a little clunky to me.
For c = 2 to LastRow
If Cells(c, Range("Product_Type").Column) = "" Then _
Cells(c, Range("Product_Type").Column).Interior.ColorIndex = 41
Next c
I've seen a similar thing somewhere where it was all done in one statement without the loop.
I am trying to creat a code that with take the value of an active cell and depending on this value will assign a backcolour to a label corresponding to the cell. so far so good.
I then want the code to offset to the next cell in the range read its value and assign a colour to that cells backcolor.
here is an idea.
range("A1").select
for n = 1 to 4
if activecell = "A" then
Label1.BackColor = RGB(0, 0, 0)
else
if activecell = "B" then
Label1.BackColor = RGB(0, 0, 255)
else
if activecell = "C" then
Label1.BackColor = RGB(0, 255, 0)
end if
activecell.offset(0,1).select
next n
Firstly I would like the next loop (refering to A2 in this example) to refer to Label2 not Label1 and so on.
secondly the example would loop through 4 cells in one row (A1:A4) but I would like the code to apply to several rows ie (A1:D4).
so thats 16 cell and 16 labels. I could code this in a very inefficient way but I am sure ther is a simple method.
Im having an awful time with the time it takes to calculate my formulas. I have probably written some of them ineffiecent.
=INDEX( Website_Query!$A$2:$Z$10000,MATCH(1,(Website_Query!$A$2:$A$9725=B1144)*(Website_Query!$C$2:$C$9725="EA "),0),4)
=Q1144*2
=IF(ISERROR(VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE)),"",VLOOKUP(A1144,pricing!A$2:AL$1547,38,FALSE))
=IF(ISERROR(Q1144+AR1144),"",Q1144+AR1144)
=IF(AK1086="q2",AL1086+AS1086,IF(AM1086="q2",AN1086+AS1086,IF(AO1086="q2",AP1086+AS1086,IF(AQ1086="q2",AR1086+AS1086,""))))
="q"&INDEX(Website_Query!$A$2:$L$7725,MATCH(1,(Website_Query!$A$2:$A$7725=$B1086)*(Website_Query!$C$2:$C$7725=$C1086),0),5)
how to calculate averages and standard deviations based on different time periods without having to manually change the cells?
example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50
2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98
3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148
and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
[Code] ......
I need to check each of 13,000 job titles to see if they include the string "VP".
For the time being, I want to find the most efficient way to check an individual title, before building an array or SUMPRODUCT function that will check all the titles in swoop.
So... I have tried:
=ISNUMBER(SEARCH(A1,B1))
..but it returns TRUE if A1 and B1 are both blank -- which they can be in my spreadsheet.
And I have tried:
=SIGN(SEARCH(A1,B1))
but it returns #N/A if A1 cannot be found within B1.
If I can, I want to avoid introducing an IF statement because, looking ahead, I am already concerned that this analysis will be complicated enough without multiple paths.
So.. what's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether A1 is in B1?
EXTRA-CREDIT!
An extension of the above problem... arrays and SUMPRODUCTs welcome:
What's the most efficient way to return a TRUE/FALSE, or 1/0, result depending on whether (A1 or A2 or A3... or A99) is in B1?
I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).
Here is an example of my macro:
Sub SimulateWeek()
If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
[Code]....
(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies View RelatedI own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
I am trying to code a macro to run down through a column and hide the row if it finds a cetain marker (i.e. the letter "n") in the column, and then move onto the next one.
View 9 Replies View RelatedMy spreadsheet calculates the date on a daily basis.
I would like to create a code in my spreadsheet which uses the year and month in question.
First, 2006 is is designated as a "3"
2007 "4"
2008 "5"
2009 "6" etc
Don't worry about anything prior to 2006.
Next, the months are represented by single digits. So, January = 1, February = 2, March = 3, April =4, etc. September is 9, and October = 10 but all the numbers over 9 have to be reduced to a single digit, so October = 1+0=1, November =1+1= 2, December =1+2= 3.
Now, the formula for the Month is:
Year + current Month
So, May 2007 = 4 + 5 = 9
June 2007 = 4 + 6 = 10 = 1
July 2007 = 4 + 7 = 11 = 2,
January 2008 = 5 + 1 = 6, etc
Is there a way of coding a macro in VBA that allows you, with one macro, to enter text in one cell and at the same time enter a value to the cell on it's right?
I don't want to name the cells, but I wondered if you could add to the code a piece of code that basically said "Select the cell to this cell's right and add the value x".
I have made this vba to go out and look at 2 different locations which contain simular folder names.
The script then writes the folder names in Column A, followed by the Size of both the folders from the 2 locations. The script is comparing the second location folder name to what the first process put in Column A so the folder names will be aligned with the file sizes. It is also converting the file sizes from bytes to the most appropriate one.
The problem I am having here, is once I put the second part in for the second folder, the script is either taking forver (More than an hour) or is freezing. So I am not sure if I put it in a loop somewhere or what exactly is happening. I am just wondering if I can get some help troubleshooting and optimizing this code....
How it is possible to lock the VBA coding and Modules? I mean when Some one want to view VBA coding the Password window should not appear, istead of that a message box appears with message something like "Not viewable"
View 9 Replies View RelatedI have a drop down box with the 10 different shifts we run here at my work. Ie - Daytime (0700-1500), Nightime (1500-2300) etc. I have these on a drop box as stated earlier (Cell 6) - and want to code a selection from within the box, to effect Cell 7 with the approriate hours worked depending on what I selected in Cell 6.
View 2 Replies View Related
