This macro accepts analytical files of constant format, imported into individual worksheets. Each file contains header information, followed by bulk analyses. Header information is extracted and used to tag individual records within an array. A unique header is created for each analytical method and erroneous values are treated. The array is output to a new worksheet, then the macro loops, repopulates the array, attempts to match the headers of the current array with those in the output worksheet and writes records in the corresponding column.
I'm particularly concerned with the section entitled "Match columns to headers" as this involves several nested loops and seems very inefficient.
As it goes, it works. However, as I intend to develop the macro further into something more versatile, it seems sensible to get the fundamentals correct.
Sub Format_Assays()
Dim batchId As String, inArray() As Variant, i As Integer, j As Integer, outRow As Integer, outCol As Integer, outSheet As Worksheet, sExist As Boolean, wSheet As Worksheet, x As Integer, xout As Integer, y As Integer, yout As Integer
'Cycle through required sheets
For Each wSheet In ActiveWorkbook.Sheets
If wSheet. Name = "Assays_All" Then
sExist = True
Exit For
End If
Next wSheet
Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro;
The four companies, A - D use have Divisions. There is some overlap in that some Divisions feature in more than one company, so there are repeating entries in Column B
I want to create a table like 3) which will have unique Division names (no repeats in Column B)
I think this can be done without VBA (and I would prefer not to use it).
I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from... 14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)
I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....
Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable
Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
I have a rather large spreadsheet that takes a very long time to calculate once the new data is added. One of the many things I need to do is look to see if a unique value in range 1 is also in range 2. If it is, return some data (vlookup), if it's not, then I want a "0", not #NA. My question is, which of these two methods will result in a faster calculation (if at all):
Option 1: Do it in one step =IF(ISNA(VLOOKUP($A3,LY,3,FALSE)),0,VLOOKUP($A3,LY,3,FALSE))
Or option 2: Do it in two steps: Column N formula: =VLOOKUP($A3,LY,3,FALSE)
Column N+1 formula: =IF(ISNA(Column N value),0,=column N value)
I have many columns of data using formulas in option 1, so if I have coded this badly that could be my problem...
I have a spreadsheet with 10,000 rows. In column A dates are mentioned and in column B I'd like to calculate the specific Quarter via VBA code as I can't have formula's in my sheet. My VBA code is:
Sub QuarterCalc() Dim myCell As Range Application. ScreenUpdating = False For Each myCell In ThisWorkbook.Sheets("Data").Range(Cells(1, 2), Cells(10000, 2)) myCell.Formula = "=INT((MONTH(RC[-1])-1)/3)+1" myCell.Offset(0, 0) = myCell.Offset(0, 0).Value Next Application.ScreenUpdating = True End Sub
I am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.
Public Sub Farvelade() Dim icolor, Navn As Integer Dim TargetRow, LastRow, Previous As Long Dim Target As Range MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen" Range("A5").Activate Range("A5"). CurrentRegion.Select 'Set range ActiveCell.Offset(Selection.Rows.Count, 0).Activate LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................
I have two worksheets. ws1 contains a large number of dates on column 3. ws3 contains around 20 dates on column 3.
I want to pick two dates randomly in ws1 that does not already exist in ws3.
Here is my working code, but it is really slow to process. How to optimize this code? Also I would like to add a function where if the macro is not able to find two random dates that aren't already in ws3 then exit loop.
VB: Set ws1 = thisworkbook.sheets(1) Set ws3 = thisworkbook.sheets(3)
lr = ws2.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row Set rvis = ws2.Range("c2:c" & lr).SpecialCells(xlCellTypeVisible) mynodate = 0
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible. If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code: Sub RSLDASHBOARDV2() 'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D. 'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D. 'Do Not Modify Code Unless Given Proper Privileges to do so. Dim APPSPD As Worksheet With Application .ScreenUpdating = False .Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code: objField2.PivotItems( _ "TRC").Position = 1 objField.PivotItems( _ "MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code: Sub STATSPIVOT() 'STATS PAGE BASED ON STATS DATA TAB Sheets("STATS DATA").Select Dim objTable As PivotTable, objField As PivotField ActiveWorkbook.Sheets("STATS DATA").Select Range("A1").Select
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define: Select File Extension Select Folder to Search Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open() Sheets("Houselist").Activate Selection.QueryTable.Refresh BackgroundQuery:=False Sheets("Front").Select Range("A1").Select End Sub
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open if workbook.name "inventorysummary.csv" then application.run "personal.xlsb!capacity" end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly. But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
I have recorded the following marco and it works fine. I would like to modify it so the user would highlight a range of cells rather than have the fixed area :
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.
